Writing Efficient SQL Aggregations: GROUP BY, HAVING, and Grouping Sets Explained

Imagine you're handed a sales database with 10 million rows — every transaction your company has processed over the past five years. Your manager wants to know which product categories are driving the most revenue, which regions are underperforming, and whether any monthly trends stand out. You can't scroll through 10 million rows. You need SQL to summarize that chaos into something a human being can actually use.

That's exactly what aggregation is for. SQL aggregations let you collapse thousands or millions of rows into meaningful summaries — totals, averages, counts, and more — grouped by whatever dimensions matter to your analysis. It sounds simple in principle, but there's a specific way SQL thinks about grouping data, and if you don't understand that mental model, you'll run into confusing errors and write queries that technically work but produce wrong answers.

By the end of this lesson, you'll be able to write aggregation queries with confidence, filter your grouped results correctly, and use advanced grouping features to produce multi-dimensional summaries in a single query — the kind of output that would normally take several separate queries or a pivot table in a spreadsheet.

What you'll learn:

• How GROUP BY works conceptually and how to use it correctly
• The five most common aggregate functions and when to reach for each one
• Why HAVING exists and why you can't just use WHERE to filter aggregated results
• How to use GROUPING SETS, ROLLUP, and CUBE to build multi-level summaries efficiently
• The most common aggregation mistakes and how to avoid them

---

Prerequisites

You should be comfortable writing basic SELECT statements with WHERE clauses and know what a table and a column are. You don't need any prior experience with aggregation — we'll build everything from scratch. The SQL in this lesson follows standard ANSI SQL and works in PostgreSQL, MySQL 8+, SQL Server, and BigQuery, with minor syntax variations noted where relevant.

---

Understanding What Aggregation Actually Does

Before touching GROUP BY, you need to understand what happens when SQL aggregates data.

Think of a raw database table as a long receipt. Every row is a single event — one sale, one click, one order line. Aggregation is the process of folding that receipt into a summary. Instead of seeing every individual transaction, you see totals and averages organized by categories you care about.

Let's say you have a table called orders that looks like this:

-- orders table
order_id | customer_id | region    | category    | amount | order_date
---------|-------------|-----------|-------------|--------|------------
1001     | 42          | Northeast | Electronics | 349.99 | 2024-01-15
1002     | 17          | Southwest | Clothing    | 89.50  | 2024-01-16
1003     | 42          | Northeast | Electronics | 199.00 | 2024-01-20
1004     | 88          | Midwest   | Clothing    | 120.00 | 2024-02-03
1005     | 17          | Southwest | Electronics | 450.00 | 2024-02-10

If you run SELECT * FROM orders, you get every row. That's useful for auditing individual transactions. But if you want to know total revenue by region, you need aggregation.

---

The Five Aggregate Functions You'll Use Most

SQL provides a set of aggregate functions — special functions that operate on a group of rows and return a single value for that group. Before learning GROUP BY, you need to know what you're aggregating.

-- COUNT: how many rows are in the group
SELECT COUNT(*) FROM orders;
-- Returns: 5

-- SUM: add up a numeric column
SELECT SUM(amount) FROM orders;
-- Returns: 1208.49

-- AVG: calculate the average value
SELECT AVG(amount) FROM orders;
-- Returns: 241.698

-- MIN and MAX: find the smallest and largest values
SELECT MIN(amount), MAX(amount) FROM orders;
-- Returns: 89.50 | 450.00

Without GROUP BY, these functions collapse the entire table into a single row. That's occasionally useful (overall totals), but usually you want a summary per category, per region, or per month. That's where GROUP BY enters.

Key insight: An aggregate function eats many rows and spits out one value. GROUP BY controls how many groups that happens in.

---

How GROUP BY Works

GROUP BY tells SQL to split your table into groups based on the values in one or more columns, and then apply your aggregate function to each group independently.

Here's the query that answers "what is total revenue by region?":

SELECT
    region,
    SUM(amount) AS total_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY region;

Result:

region    | total_revenue | order_count
----------|---------------|------------
Northeast | 548.99        | 2
Southwest | 539.50        | 2
Midwest   | 120.00        | 1

Here's what SQL is actually doing step by step:

1. It scans the orders table
2. It finds all unique values in the region column: Northeast, Southwest, Midwest
3. It creates one "bucket" for each unique value
4. It routes every row into the matching bucket
5. It runs SUM(amount) and COUNT(*) on each bucket separately
6. It returns one output row per bucket

This is the mental model that will save you from 90% of aggregation errors.

The SELECT-GROUP BY Rule

Here's a rule that trips up almost everyone when they start writing aggregations:

Every column in your SELECT clause must either be in your GROUP BY clause, or be wrapped in an aggregate function.

Why? Think about it from SQL's perspective. When you group by region, SQL is collapsing multiple rows into one. If your Northeast bucket contains two rows with order_id values of 1001 and 1003, and you ask SQL to show you order_id in the output — which one should it show? SQL doesn't know, and rather than guess, it throws an error (in most databases).

-- This will error in PostgreSQL and SQL Server
SELECT region, order_id, SUM(amount)
FROM orders
GROUP BY region;

-- ERROR: column "orders.order_id" must appear in the GROUP BY
-- clause or be used in an aggregate function

The fix is either to add order_id to GROUP BY (if you want a row per region+order combination) or remove it from SELECT (if you only want region totals).

MySQL note: MySQL in its default mode is more permissive here — it will run the query and just pick an arbitrary value for order_id. This is dangerous because it silently produces wrong answers. Always enable STRICT_MODE or ONLY_FULL_GROUP_BY in MySQL.

Grouping by Multiple Columns

You can group by as many columns as you need. SQL will create a bucket for each unique combination of values.

SELECT
    region,
    category,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY region, category
ORDER BY region, category;

Result:

region    | category    | total_revenue | avg_order_value
----------|-------------|---------------|----------------
Midwest   | Clothing    | 120.00        | 120.00
Northeast | Electronics | 548.99        | 274.495
Southwest | Clothing    | 89.50         | 89.50
Southwest | Electronics | 450.00        | 450.00

Notice that the Northeast+Electronics combination has two orders that got summed together (349.99 + 199.00 = 548.99).

Grouping by Expressions

You're not limited to grouping by raw column values. You can group by expressions — including date truncation, which is extremely common in time-series analysis.

-- Revenue by month
SELECT
    DATE_TRUNC('month', order_date) AS order_month,
    SUM(amount) AS monthly_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

In SQL Server, you'd use DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) instead. In BigQuery, DATE_TRUNC(order_date, MONTH) works similarly.

---

Filtering Groups with HAVING

Once you have your grouped results, you'll often want to filter them — maybe you only care about regions with more than 5 orders, or categories where average order value exceeds $200.

Your instinct might be to use WHERE. That instinct is wrong here, and understanding why is important.

WHERE filters individual rows before grouping happens. By the time GROUP BY runs, WHERE has already eliminated rows. That means you can't use WHERE to reference the result of an aggregate function — that result doesn't exist yet when WHERE is evaluated.

HAVING filters groups after grouping happens. It runs on the output of GROUP BY, which means you can reference aggregate functions freely.

-- Find regions where total revenue exceeds $300
SELECT
    region,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY region
HAVING SUM(amount) > 300;

Result:

region    | total_revenue
----------|---------------
Northeast | 548.99
Southwest | 539.50

Midwest ($120) is excluded because it didn't pass the HAVING filter.

WHERE vs. HAVING: Knowing Which One to Use

The distinction becomes clear when you understand the order SQL actually executes your query:

1. FROM — identify the source table(s)
2. WHERE — filter individual rows
3. GROUP BY — group remaining rows
4. HAVING — filter groups
5. SELECT — compute output columns
6. ORDER BY — sort results
7. LIMIT / FETCH — restrict row count

You can (and often should) use both WHERE and HAVING in the same query. Use WHERE to eliminate rows you don't want included in the grouping at all, and use HAVING to eliminate groups that don't meet your threshold.

-- Only analyze orders from 2024
-- AND only show categories with more than 1 order

SELECT
    category,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2024-01-01'    -- row-level filter: applied first
GROUP BY category
HAVING COUNT(*) > 1                  -- group-level filter: applied after grouping
ORDER BY total_revenue DESC;

Performance tip: Always push as much filtering as possible into WHERE rather than HAVING. WHERE eliminates rows before grouping, which reduces the work the database has to do. HAVING filters after the aggregation is complete, so the database still has to process those rows.

---

Advanced Grouping: GROUPING SETS, ROLLUP, and CUBE

Here's a real-world scenario: your finance team wants a single report that shows:

• Revenue by region and category (the detailed breakdown)
• Revenue by region only (regional subtotals)
• Revenue by category only (category subtotals)
• Overall total revenue (grand total)

One approach is to write four separate queries and union them together. That works, but it's verbose and makes the database scan the table four times. SQL provides a better solution: grouping sets.

GROUPING SETS

GROUPING SETS lets you define multiple grouping configurations in a single query. The database computes each grouping and unions the results together, but with only one table scan.

SELECT
    region,
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY GROUPING SETS (
    (region, category),   -- detailed: one row per region+category combo
    (region),             -- subtotal: one row per region
    (category),           -- subtotal: one row per category
    ()                    -- grand total: one row for everything
)
ORDER BY region, category;

Result:

region    | category    | total_revenue
----------|-------------|---------------
Midwest   | Clothing    | 120.00
Northeast | Electronics | 548.99
Southwest | Clothing    | 89.50
Southwest | Electronics | 450.00
NULL      | Clothing    | 209.50         ← category subtotal (region is NULL)
NULL      | Electronics | 998.99         ← category subtotal
Midwest   | NULL        | 120.00         ← region subtotal (category is NULL)
Northeast | NULL        | 548.99         ← region subtotal
Southwest | NULL        | 539.50         ← region subtotal
NULL      | NULL        | 1208.49        ← grand total

Notice that NULL values appear where a dimension isn't part of that particular grouping. A NULL in the region column means "this row is a summary across all regions." This is different from a NULL that appears in the actual data — it's a structural NULL indicating aggregation.

You can distinguish structural NULLs from data NULLs using the GROUPING() function:

SELECT
    CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END AS region,
    CASE WHEN GROUPING(category) = 1 THEN 'All Categories' ELSE category END AS category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY GROUPING SETS (
    (region, category),
    (region),
    (category),
    ()
);

GROUPING(column) returns 1 when that column is not part of the current grouping (i.e., it's a structural NULL), and 0 when it is.

ROLLUP

ROLLUP is a shortcut for a common pattern: a hierarchy of subtotals from most detailed to grand total.

GROUP BY ROLLUP(region, category) is equivalent to:

GROUP BY GROUPING SETS (
    (region, category),
    (region),
    ()
)

It adds subtotals at each level of the hierarchy, plus a grand total. It does not add a standalone category subtotal — the hierarchy goes left to right.

SELECT
    region,
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY ROLLUP(region, category)
ORDER BY region, category;

Use ROLLUP when your dimensions have a natural hierarchy — like year → month → day, or country → region → city.

CUBE

CUBE generates subtotals for every possible combination of the specified columns, including all individual subtotals and the grand total.

GROUP BY CUBE(region, category) is equivalent to:

GROUP BY GROUPING SETS (
    (region, category),
    (region),
    (category),
    ()
)

That's exactly what we wrote manually in the GROUPING SETS example above. CUBE is the fastest way to get a full cross-tabulation of your dimensions.

SELECT
    region,
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY CUBE(region, category);

Warning: CUBE can generate a lot of rows. With 3 dimensions, you get 2³ = 8 grouping combinations. With 5 dimensions, that's 32 combinations. Be thoughtful about how many dimensions you throw into a CUBE.

---

Hands-On Exercise

Work through these exercises using the orders table defined at the start of this lesson. You can create it yourself:

CREATE TABLE orders (
    order_id    INT,
    customer_id INT,
    region      VARCHAR(50),
    category    VARCHAR(50),
    amount      DECIMAL(10,2),
    order_date  DATE
);

INSERT INTO orders VALUES
(1001, 42, 'Northeast', 'Electronics', 349.99, '2024-01-15'),
(1002, 17, 'Southwest', 'Clothing',     89.50, '2024-01-16'),
(1003, 42, 'Northeast', 'Electronics', 199.00, '2024-01-20'),
(1004, 88, 'Midwest',   'Clothing',    120.00, '2024-02-03'),
(1005, 17, 'Southwest', 'Electronics', 450.00, '2024-02-10'),
(1006, 55, 'Northeast', 'Clothing',     75.00, '2024-02-14'),
(1007, 88, 'Midwest',   'Electronics', 310.00, '2024-03-01'),
(1008, 42, 'Southwest', 'Clothing',    200.00, '2024-03-08');

Exercise 1: Write a query that returns the number of orders and total revenue for each customer. Sort by total revenue descending.

Exercise 2: Find every region-category combination where the average order value is above $200. Return the region, category, average order value, and order count.

Exercise 3: Write a query that shows total revenue by category, but only include orders placed in February 2024 or later. Use WHERE for the date filter, not HAVING.

Exercise 4: Use ROLLUP to produce a report showing revenue broken down by region and category, with regional subtotals and a grand total.

Exercise 5 (challenge): Modify your Exercise 4 query to replace structural NULL values with descriptive labels like "All Categories" and "Grand Total" using the GROUPING() function and CASE expressions.

---

Common Mistakes & Troubleshooting

Mistake 1: Filtering aggregates with WHERE

-- WRONG: WHERE can't see aggregate results
SELECT region, SUM(amount)
FROM orders
WHERE SUM(amount) > 300
GROUP BY region;

-- RIGHT: Use HAVING for aggregate filters
SELECT region, SUM(amount)
FROM orders
GROUP BY region
HAVING SUM(amount) > 300;

Mistake 2: Forgetting to include non-aggregated columns in GROUP BY

-- WRONG: category is selected but not grouped
SELECT region, category, SUM(amount)
FROM orders
GROUP BY region;

-- RIGHT: include every non-aggregated column
SELECT region, category, SUM(amount)
FROM orders
GROUP BY region, category;

Mistake 3: Misinterpreting NULLs in GROUPING SETS output

When you see NULL in a GROUPING SETS result, it might mean "this is an aggregate row, the dimension doesn't apply" — or it might mean the actual data has a NULL value in that column. Use GROUPING() to tell the difference, and consider adding WHERE column IS NOT NULL in your source data if data NULLs would contaminate your groupings.

Mistake 4: COUNT(*) vs COUNT(column)

COUNT(*) counts all rows in the group, including rows with NULL values. COUNT(column) counts only the rows where that column is not NULL. These produce different results if your column has NULLs.

-- Counts all orders
SELECT COUNT(*) FROM orders;

-- Counts only orders where customer_id is not NULL
SELECT COUNT(customer_id) FROM orders;

Mistake 5: Using HAVING when WHERE is appropriate (and faster)

-- SLOWER: HAVING filters after grouping all rows
SELECT region, SUM(amount)
FROM orders
GROUP BY region
HAVING region = 'Northeast';

-- FASTER: WHERE filters before grouping
SELECT region, SUM(amount)
FROM orders
WHERE region = 'Northeast'
GROUP BY region;

If your filter doesn't involve an aggregate function, it should almost always be a WHERE clause.

---

Summary & Next Steps

You've covered the full aggregation toolkit that working data professionals rely on every day. Here's what you now know:

• Aggregate functions (SUM, COUNT, AVG, MIN, MAX) collapse multiple rows into a single value
• GROUP BY splits your data into buckets and applies aggregate functions to each bucket independently
• Every column in SELECT must either be in GROUP BY or wrapped in an aggregate function
• HAVING filters the output of GROUP BY — use it when your filter references an aggregate result; use WHERE when it doesn't
• GROUPING SETS lets you specify multiple grouping configurations in one query
• ROLLUP generates a hierarchy of subtotals from most detailed to grand total
• CUBE generates subtotals for every possible combination of your dimensions
• The GROUPING() function distinguishes structural NULLs from data NULLs in multi-level aggregation results

Where to go from here:

The natural next step is learning window functions — which let you perform calculations across rows without collapsing them into groups. Window functions are to GROUP BY what a rolling average is to a total: they give you the aggregate context without losing the individual row detail.

You should also explore CTEs (Common Table Expressions), which let you build aggregations in layers — aggregate once into a CTE, then query that CTE as if it were a table, which keeps complex analytical queries readable and maintainable.

Finally, practice reading query execution plans in your database of choice. Understanding how your database executes a GROUP BY — whether it uses a hash aggregate or a sort aggregate — will help you write queries that run fast even on large tables.