Aggregating Data with GROUP BY and HAVING

One of the most powerful capabilities in SQL is the ability to move beyond row-by-row retrieval and instead produce summary statistics across meaningful categories of data. The GROUP BY clause is the engine behind this capability, and the HAVING clause is its companion filter. Together, they allow you to answer questions like "What is the total revenue per region?", "Which product categories have more than 500 orders?", or "What is the average salary in each department, and which departments exceed the company average?" Understanding these two clauses — how they work individually, how they interact with each other, and how they fit into the broader query execution pipeline — is fundamental to writing meaningful analytical SQL.

Purpose of GROUP BY

At its core, GROUP BY instructs the database engine to collapse multiple rows that share identical values in one or more specified columns into a single representative row. Instead of returning every individual transaction, order, or employee record, the query returns one row per group, paired with aggregate calculations that summarize all the rows within that group.

Consider a table called orders with the following sample data:

A query grouping by region would collapse the six rows into two group rows — one for North and one for South — and any aggregate applied in the SELECT clause would summarize the rows within each group:

SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region;

Result:

A critically important rule governs what you are allowed to place in the SELECT list when using GROUP BY: every column in the SELECT list that is not wrapped inside an aggregate function must appear in the GROUP BY clause. This rule exists because the database is collapsing many rows into one. If a column is not part of the grouping key and is not being aggregated, the database has no single unambiguous value to display for that column. Attempting to select such a column will produce an error in strict SQL implementations (such as PostgreSQL and MySQL in strict mode), or unpredictable results in more permissive systems.

For example, the following query would be invalid, because order_id is not in the GROUP BY clause and is not aggregated:

-- INVALID: order_id is not in GROUP BY and not aggregated
SELECT region, order_id, SUM(amount)
FROM orders
GROUP BY region;

To add more granularity, you can include multiple columns in the GROUP BY clause. This creates a separate group for each unique combination of values across all listed columns. Grouping by both region and product_category would produce a row for every region-category pairing that exists in the data:

SELECT region, product_category, SUM(amount) AS total_sales
FROM orders
GROUP BY region, product_category;

Common Aggregate Functions Used with GROUP BY

Aggregate functions are the computations that summarize all rows within a group into a single output value. They are the reason GROUP BY is so useful. The most common ones are COUNT(), SUM(), AVG(), MIN(), and MAX().

• COUNT() — Counts the number of rows in each group. There is an important distinction between its two forms. COUNT(*) counts every row in the group, including rows that contain NULL values in any column. COUNT(column_name), by contrast, counts only the rows where that specific column is not NULL. This distinction matters when a column has missing data. For example, COUNT(*) on a group of 10 rows always returns 10, whereas COUNT(phone_number) on those same rows returns however many rows actually have a phone number recorded.
• SUM() — Adds up all non-NULL numeric values in the specified column for each group. NULL values are simply ignored. If every value in the group is NULL, SUM() returns NULL. This function is most naturally applied to monetary amounts, quantities, or any additive numeric measure.
• AVG() — Computes the arithmetic mean of all non-NULL numeric values within a group. Conceptually it divides the sum of non-NULL values by the count of non-NULL values. This means that NULL entries are excluded from both the numerator and the denominator, which is different from how you might manually calculate an average if you think of NULL as zero.
• MIN() and MAX() — Return the smallest and largest values in a group respectively. These functions are more versatile than they might first appear: they work correctly on numeric data, date/time data (earliest and latest dates), and even string data (alphabetically first and last). For example, MIN(hire_date) gives you the earliest hire date in each department group, and MAX(last_name) gives you the alphabetically last surname in each group.

A single query can use multiple aggregate functions simultaneously, producing several summary columns at once:

SELECT
    region,
    COUNT(*)          AS order_count,
    SUM(amount)       AS total_sales,
    AVG(amount)       AS avg_order_value,
    MIN(amount)       AS smallest_order,
    MAX(amount)       AS largest_order
FROM orders
GROUP BY region;

Filtering Groups with the HAVING Clause

Once GROUP BY has organized rows into groups and aggregates have been computed, you often want to keep only those groups that meet some condition — for example, only regions with more than two orders, or only product categories where total sales exceed a threshold. This is precisely what HAVING is for.

HAVING is written after the GROUP BY clause and before any ORDER BY clause. Its syntax mirrors that of a WHERE clause, but the key distinction is that it operates on group-level results, not on individual rows. Because of this, you can use aggregate functions directly inside a HAVING condition — something that is not permitted in a WHERE clause.

SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
HAVING SUM(amount) > 500;

This query first groups rows by region and sums the amounts. The HAVING clause then removes any group where the sum is 500 or less. Only North (total 800.00) passes the filter; South (total 475.00) is excluded.

Just as with WHERE, HAVING supports multiple conditions connected with AND and OR. This allows you to define sophisticated group-level filters. For instance, you might want regions that have at least two orders and a total sales amount greater than 400:

SELECT region, COUNT(*) AS order_count, SUM(amount) AS total_sales
FROM orders
GROUP BY region
HAVING COUNT(*) >= 2 AND SUM(amount) > 400;

Both North (3 orders, $800) and South (3 orders, $475) would satisfy this particular condition, so both would appear in the result.

WHERE vs. HAVING: Knowing When to Use Each

This is one of the most common sources of confusion for SQL learners, and mastering the distinction is essential. The key is understanding when in the query lifecycle each clause acts.

WHERE filters individual rows from the source table before any grouping or aggregation takes place. Think of it as a gatekeeper that decides which raw records are even eligible to participate in the grouping step. Because aggregation has not happened yet when WHERE is evaluated, aggregate functions like SUM() or COUNT() are not allowed inside a WHERE clause. The database simply does not yet know what the group totals are.

HAVING filters groups after GROUP BY has already formed them and after aggregates have been calculated. It operates on the summarized, one-row-per-group result set. Because aggregation has already occurred, aggregate functions are perfectly valid inside HAVING.

Critically, both clauses can appear in the same query, and they do different jobs. Here is a practical example: suppose you want to find regions where total sales of Electronics only exceed $500. You need WHERE to restrict to Electronics rows first, and then HAVING to filter on the aggregated total:

SELECT region, SUM(amount) AS electronics_sales
FROM orders
WHERE product_category = 'Electronics'
GROUP BY region
HAVING SUM(amount) > 500;

The WHERE clause removes all Clothing rows before grouping — they will not contribute to any group's count or sum. The GROUP BY then forms groups from the remaining Electronics rows, and HAVING filters out any region group whose Electronics total does not exceed 500. This is fundamentally different from putting the category filter in a HAVING clause, which would be incorrect and potentially much less efficient, because all rows would first be grouped before filtering.

Query Execution Order with GROUP BY and HAVING

SQL queries are not executed in the order you write them. Understanding the logical execution order — sometimes called the order of operations or logical processing order — is essential for writing correct queries and for diagnosing subtle bugs.

The logical order is as follows:

• 1. FROM (and JOIN) — The database assembles the complete working dataset by identifying the source table(s) and applying any joins. This is the full pool of rows available to the rest of the query.
• 2. WHERE — Individual row filters are applied to the full dataset, discarding rows that do not match the condition. This reduces the number of rows that will participate in the grouping step.
• 3. GROUP BY — The remaining rows are organized into groups based on the distinct combinations of values in the grouping columns. Each group will eventually produce one row in the output.
• 4. HAVING — Aggregate conditions are evaluated for each group, and groups that do not satisfy the HAVING condition are eliminated.
• 5. SELECT — The output columns are assembled, including evaluating any aggregate functions (if not already computed) and applying column aliases.
• 6. ORDER BY — The final result set is sorted. Note that ORDER BY can reference column aliases defined in the SELECT clause, because it executes after SELECT.
• 7. LIMIT / FETCH / TOP (if present) — The result set is trimmed to the requested number of rows.

This order has practical consequences. For instance, you cannot reference a SELECT alias inside a WHERE or HAVING clause in many databases, because those clauses are logically processed before the SELECT aliases are defined. (Some databases like MySQL and SQLite do allow alias references in HAVING as an extension.) To stay safe and portable, reference the original expression or column name in WHERE and HAVING rather than the alias:

-- Portable and always correct:
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
HAVING SUM(amount) > 500;  -- Use the expression, not the alias

-- May work in some databases but is not universally portable:
HAVING total_sales > 500;

Writing Combined GROUP BY and HAVING Queries

Building a query that correctly uses GROUP BY and HAVING together is a skill that becomes natural with a methodical approach. A reliable strategy is to work through the query in several incremental steps.

Step 1 — Identify your grouping columns and your aggregate measure. Ask yourself: what categories do I want one summary row per? And what numerical measure do I want to summarize? For example: "I want one row per department, showing total salary expenditure."

Step 2 — Write the SELECT and GROUP BY clauses first. Put the grouping column(s) in both SELECT and GROUP BY, and add the aggregate expression in SELECT.

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Step 3 — Run this intermediate query to verify that the groupings look correct and that the aggregate values make sense before adding further complexity.

Step 4 — Add the HAVING clause with the business condition. For example, "only show departments where total salary exceeds $200,000."

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 200000;

Step 5 — If needed, add a WHERE clause to pre-filter rows before grouping. For example, "only consider full-time employees."

SELECT department, SUM(salary) AS total_salary
FROM employees
WHERE employment_type = 'Full-Time'
GROUP BY department
HAVING SUM(salary) > 200000;

Step 6 — Add ORDER BY to sort the results meaningfully. Sorting by the aggregate value descending is common in ranked reporting or "top N" style outputs.

SELECT department, SUM(salary) AS total_salary
FROM employees
WHERE employment_type = 'Full-Time'
GROUP BY department
HAVING SUM(salary) > 200000
ORDER BY total_salary DESC;

This full query, read as a business statement, says: "From the employees table, considering only full-time staff, show the total salary cost per department, but only for departments whose total exceeds $200,000, ranked from highest to lowest." The incremental build approach — write the grouping first, verify, then add HAVING, then ORDER BY — catches logical errors early and makes debugging straightforward.

Here is a final illustrative example that brings all the concepts together. Suppose we have a sales table with columns salesperson_id, sale_date, product_category, and revenue. We want to find the top-performing salespersons who, in 2024 alone, sold Electronics with a total revenue above $10,000, and we want to see how many transactions they completed:

SELECT
    salesperson_id,
    COUNT(*)        AS num_transactions,
    SUM(revenue)    AS total_revenue,
    AVG(revenue)    AS avg_transaction_value
FROM sales
WHERE product_category = 'Electronics'
  AND sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY salesperson_id
HAVING SUM(revenue) > 10000
ORDER BY total_revenue DESC;

Walking through the execution: WHERE first removes all rows that are not Electronics or not in 2024. GROUP BY then collapses the remaining rows into one group per salesperson. HAVING eliminates any salesperson whose 2024 Electronics revenue totals $10,000 or less. SELECT computes the count, sum, and average for each surviving group. ORDER BY sorts the final output from highest earner to lowest. The result is a clean, business-ready leaderboard built entirely in SQL.