GROUP BY and Aggregate Functions: HAVING, Conditional Aggregation, GROUPING SETS and ROLLUP
GROUP BY collapses rows into groups, aggregates ignore NULL, and in SQL Server AVG over an integer column returns an integer. We show COUNT(*) versus COUNT(column), where WHERE and HAVING belong, STRING_AGG, conditional aggregation with CASE, a PIVOT comparison, and ROLLUP, CUBE, GROUPING SETS with GROUPING() on Bookstore data with real output.
Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, CU10 (Cumulative Update 10)) against the series' Bookstore database; dialect comparisons were tried on PostgreSQL 18.6 and MySQL 8.4.
Guess First
Bookstore has 40 customers and 181 reviews. Write down what the two queries below return, then keep reading:
SELECT COUNT(*) AS customers,
COUNT(phone) AS with_phone,
COUNT(city) AS with_city,
COUNT(DISTINCT city) AS distinct_cities
FROM dbo.customers;
SELECT AVG(rating) AS avg_rating FROM dbo.reviews;All four columns in the first query ask "how many", but they do not ask the same question. The second query is an innocent average; reviews carry a rating from 1 to 5 and most of them are 4 or 5. Here is the output:
customers with_phone with_city distinct_cities
--------- ---------- --------- ---------------
40 19 37 10
avg_rating
----------
4COUNT(*) is 40, COUNT(phone) 19, COUNT(city) 37, COUNT(DISTINCT city) 10. The average rating is 4. Exactly 4, even though the real average is 4.116. This post starts with the rules behind those two surprises and continues with GROUP BY, HAVING, conditional aggregation and the ROLLUP family, which together carry almost all day-to-day reporting.
GROUP BY Collapses Rows into Groups
An aggregate function takes many rows and returns a single value: COUNT, SUM, AVG, MIN, MAX. Without GROUP BY the whole table is one group, which is why the queries above returned a single row. Add GROUP BY and rows that carry the same value collapse into a group, producing one row per group:
SELECT category_id, COUNT(*) AS books, MIN(list_price) AS cheapest, MAX(list_price) AS priciest
FROM dbo.books
GROUP BY category_id
ORDER BY category_id;category_id books cheapest priciest
----------- ----- -------- --------
NULL 2 210.00 670.50
2 29 200.00 680.50
3 9 390.50 660.90
4 4 320.90 580.50
5 6 200.50 600.00
6 6 290.90 650.00
9 5 200.50 600.00
11 9 890.00 1450.00
12 2 1150.00 1450.00
13 5 200.00 670.90Notice two things. The two books without a category (category_id is NULL) formed their own group: for GROUP BY, all NULLs are one group. That looks like it contradicts the three-valued logic from post 06 of this series, but here the standard deliberately uses "not distinct" instead of "equal". Second, the result came back in category_id order because I wrote ORDER BY; without it there would be no ordering guarantee at all. We will come back to that in Production Notes.
Now we want to show the category name too. The first attempt is what most developers write:
SELECT c.category_id, c.name, COUNT(*) AS books
FROM dbo.categories AS c
JOIN dbo.books AS b ON b.category_id = c.category_id
GROUP BY c.category_id;Msg 8120, Level 16, State 1, Server b8be943222fa, Line 1
Column 'dbo.categories.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.The rule: every expression in the SELECT list must either sit inside an aggregate or be guaranteed to carry a single value per group. SQL Server only accepts that guarantee when the expression appears in the GROUP BY list. Yet category_id is the primary key, so name is already unique per group. The ANSI (American National Standards Institute) and ISO (International Organization for Standardization) standard has recognised this as a "functional dependency" since SQL:1999, and PostgreSQL accepts dependencies that come from a primary key: the same query ran without error on PostgreSQL 18. In SQL Server you add name to GROUP BY as well:
SELECT c.category_id, c.name, COUNT(*) AS books
FROM dbo.categories AS c
JOIN dbo.books AS b ON b.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY books DESC, c.category_id;category_id name books
----------- --------------- -----
2 Classics 29
3 Science Fiction 9
11 Programming 9
5 Fantasy 6
6 Mystery 6
9 Science 5
13 Children 5
4 Dystopian 4
12 Algorithms 2MySQL changed over time. Since 5.7.5 the default sql_mode includes ONLY_FULL_GROUP_BY, and on MySQL 8.4 selecting an ungrouped column gave me error 1055. If an old project switched that mode off, MySQL returns a value from any row of the group for that column; the query does not fail, but the result may be wrong. SQL:2023's ANY_VALUE aggregate is the standard way to state that "any one will do" intent explicitly; PostgreSQL has supported it since 16, SQL Server 2025 does not ('ANY_VALUE' is not a recognized built-in function name).
Aggregate Functions Do Not See NULL
The answer to the first surprise is one sentence: COUNT(*) counts rows, COUNT(column) counts the non-NULL values of that column. 21 customers have a NULL phone, so COUNT(phone) returned 19. COUNT(DISTINCT city) drops NULLs and then counts the distinct values. SUM, AVG, MIN and MAX ignore NULLs in the same way.
That has a sneaky consequence: when no rows arrive, COUNT returns 0 but the others return NULL. For The Mythical Man-Month (book_id 62), which was never ordered:
SELECT COUNT(*) AS cnt, SUM(quantity) AS total_qty, AVG(unit_price) AS avg_price
FROM dbo.order_items
WHERE book_id = 62;cnt total_qty avg_price
--- --------- ---------
0 NULL NULLIf the application reads this value into a decimal and is not ready for NULL, it blows up on the first unsold book. If you want 0 in the report, write COALESCE(SUM(quantity), 0); but think twice before turning an average into 0, because "no average" and "an average of zero" are not the same thing.
The second surprise is AVG's integer division. In SQL Server the return type of AVG is derived from the input type: int for tinyint, smallint and int. rating is tinyint, so 745 / 181 = 4.116 was computed and truncated to 4. The fix is to widen the type before aggregating:
SELECT AVG(rating) AS avg_int,
AVG(rating * 1.0) AS avg_numeric,
AVG(CAST(rating AS decimal(3,2))) AS avg_decimal,
SUM(rating) AS total,
COUNT(*) AS reviews
FROM dbo.reviews;avg_int avg_numeric avg_decimal total reviews
------- ----------- ----------- ----- -------
4 4.116022 4.116022 745 181The standard leaves the scale of an integer average implementation-defined, and the dialects decide differently. In PostgreSQL the average of an integer column is numeric, in MySQL it is decimal: in a small test with ratings 4, 5 and 4, PostgreSQL gave 4.3333333333333333 and MySQL 4.3333. So a report you write on SQL Server "gets fixed" when moved to PostgreSQL, and silently breaks in the other direction. Make it your own rule: when you average an integer column, always widen the type explicitly.
WHERE or HAVING?
Remember the logical query processing order from post 05 of this series: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters individual rows before groups exist; HAVING filters groups after they are formed. That is why you cannot use an aggregate inside WHERE: there are no groups at that point:
SELECT customer_id, COUNT(*) AS orders
FROM dbo.orders
WHERE COUNT(*) >= 20
GROUP BY customer_id;Msg 147, Level 15, State 1, Server b8be943222fa, Line 3
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.The correct query puts each filter where it belongs: cancelled orders are removed before grouping, customers with fewer than 15 orders after grouping:
SELECT customer_id, COUNT(*) AS orders
FROM dbo.orders
WHERE status <> 'Cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 15
ORDER BY orders DESC;customer_id orders
----------- ------
1 37
3 33
32 30
21 23
2 22
14 21
30 21
22 19A practical rule: put every condition that does not look at an aggregate into WHERE. HAVING customer_id <> 5 also works, but it feeds unnecessary rows into grouping and makes the reader ask "is this condition about the group or the row?". The optimizer pushes such a condition down by itself in simple cases; writing your intent in the right place is still your job.
Collapsing a List into One Row with STRING_AGG
Sometimes you want a list from a group rather than a number: the authors of multi-author books, for example. STRING_AGG (SQL Server 2017+) concatenates values with a separator, and WITHIN GROUP (ORDER BY ...) fixes the order:
SELECT b.title,
STRING_AGG(a.last_name, ', ') WITHIN GROUP (ORDER BY ba.author_order) AS authors
FROM dbo.books AS b
JOIN dbo.book_authors AS ba ON ba.book_id = b.book_id
JOIN dbo.authors AS a ON a.author_id = ba.author_id
GROUP BY b.book_id, b.title
HAVING COUNT(*) > 1
ORDER BY b.book_id;title authors
------------------------------------------------- -------------------------------
Good Omens Pratchett, Gaiman
The C Programming Language Kernighan, Ritchie
The Pragmatic Programmer Hunt, Thomas
Design Patterns Gamma, Helm, Johnson, Vlissides
Structure and Interpretation of Computer Programs Abelson, Sussman
The Elements of Style Strunk, WhiteWithout WITHIN GROUP the order is not guaranteed; one day Design Patterns may come back as "Helm, Gamma, ...". The function the standard defines for this job is LISTAGG from SQL:2016; SQL Server and PostgreSQL offer their own STRING_AGG instead (both reported LISTAGG as an unknown function), and MySQL uses GROUP_CONCAT. One idea, three syntaxes: we compare dialect differences together in post 16.
Conditional Aggregation: A Monthly Report in One Query
This is the most useful reporting technique there is: put a CASE inside the aggregate so that every column gets its own filter. The query below breaks the second half of 2025 down month by month; for each month the total orders, the cancellations, and the orders from Turkey and from abroad are computed in a single pass:
SELECT DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1) AS month_start,
COUNT(*) AS orders,
COUNT(CASE WHEN o.status = 'Cancelled' THEN 1 END) AS cancelled,
SUM(CASE WHEN c.country_code = 'TR' THEN 1 ELSE 0 END) AS tr_orders,
SUM(CASE WHEN c.country_code <> 'TR' THEN 1 ELSE 0 END) AS foreign_orders
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id
WHERE o.ordered_at >= '2025-07-01' AND o.ordered_at < '2026-01-01'
GROUP BY DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1)
ORDER BY month_start;month_start orders cancelled tr_orders foreign_orders
----------- ------ --------- --------- --------------
2025-07-01 14 2 7 7
2025-08-01 17 0 12 5
2025-09-01 34 4 17 17
2025-10-01 24 1 19 5
2025-11-01 23 0 15 8
2025-12-01 42 2 24 18I used two idioms and both do the same job. In COUNT(CASE WHEN ... THEN 1 END) there is no ELSE, so non-matching rows become NULL and COUNT does not count NULL. SUM(CASE WHEN ... THEN 1 ELSE 0 END) adds 0 for the non-matching rows. For the month column I produced the first day of the month with DATEFROMPARTS; that way the result sorts as a real date and can travel to the application as a DateOnly.
The standard defines a more readable syntax for this: COUNT(*) FILTER (WHERE status = 'Cancelled'). PostgreSQL supports it; SQL Server 2025 answers with Incorrect syntax near the keyword 'WHERE', so the CASE idiom is the only portable way.
The same technique turns rows into columns. Monthly collections by payment method:
SELECT DATEFROMPARTS(YEAR(paid_at), MONTH(paid_at), 1) AS month_start,
SUM(CASE WHEN method = 'card' THEN amount ELSE 0 END) AS card,
SUM(CASE WHEN method = 'bank_transfer' THEN amount ELSE 0 END) AS bank_transfer,
SUM(CASE WHEN method = 'gift_card' THEN amount ELSE 0 END) AS gift_card
FROM dbo.payments
WHERE paid_at >= '2025-10-01' AND paid_at < '2026-01-01'
GROUP BY DATEFROMPARTS(YEAR(paid_at), MONTH(paid_at), 1)
ORDER BY month_start;month_start card bank_transfer gift_card
----------- -------- ------------- ---------
2025-10-01 21633.50 4856.40 1000.00
2025-11-01 16533.50 3392.30 400.00
2025-12-01 28328.01 12338.19 1200.00T-SQL (Transact-SQL) also offers the PIVOT operator for this, and it produces the same result:
SELECT month_start, [card], [bank_transfer], [gift_card]
FROM (
SELECT DATEFROMPARTS(YEAR(paid_at), MONTH(paid_at), 1) AS month_start, method, amount
FROM dbo.payments
WHERE paid_at >= '2025-10-01' AND paid_at < '2026-01-01'
) AS src
PIVOT (SUM(amount) FOR method IN ([card], [bank_transfer], [gift_card])) AS p
ORDER BY month_start;month_start card bank_transfer gift_card
----------- -------- ------------- ---------
2025-10-01 21633.50 4856.40 1000.00
2025-11-01 16533.50 3392.30 400.00
2025-12-01 28328.01 12338.19 1200.00I prefer conditional aggregation. PIVOT takes only one aggregate, wants a fixed column list, is not standard, and implicitly groups by "everything else" in the source; add one extra column to the derived table and the result silently splits into more rows. With the CASE idiom, what is grouped is written explicitly in GROUP BY, and you can mix SUM, COUNT and AVG in the same query.
ROLLUP, CUBE and GROUPING SETS
Reports usually want subtotals: revenue by country and year, each country's total, and a grand total. You could write three queries and glue them together with UNION ALL, but ROLLUP, added to the standard in SQL:1999, does it in one:
SELECT c.country_code,
YEAR(o.ordered_at) AS order_year,
COUNT(DISTINCT o.order_id) AS orders,
SUM(oi.quantity * oi.unit_price) AS revenue,
GROUPING(c.country_code) AS g_country,
GROUPING(YEAR(o.ordered_at)) AS g_year
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'Cancelled'
GROUP BY ROLLUP (c.country_code, YEAR(o.ordered_at))
ORDER BY GROUPING(c.country_code), c.country_code, GROUPING(YEAR(o.ordered_at)), order_year;country_code order_year orders revenue g_country g_year
------------ ---------- ------ --------- --------- ------
DE 2025 61 57102.36 0 0
DE 2026 26 32246.84 0 0
DE NULL 87 89349.20 0 1
GB 2025 31 26911.82 0 0
GB 2026 12 12485.15 0 0
GB NULL 43 39396.97 0 1
NL 2025 4 5518.40 0 0
NL 2026 2 1880.50 0 0
NL NULL 6 7398.90 0 1
TR 2025 151 149683.15 0 0
TR 2026 68 76318.86 0 0
TR NULL 219 226002.01 0 1
NULL NULL 355 362147.08 1 1ROLLUP (country_code, order_year) produces three grouping sets: (country, year), (country) and (), the grand total. In subtotal rows the column that was not grouped shows NULL; the way to tell that NULL apart from a real NULL value is the GROUPING() function, which returns 1 when the column did not take part in the grouping for that row. I used GROUPING() in the ordering too, so that totals land under their own group. Also note COUNT(DISTINCT o.order_id): because I joined order_items, each order was multiplied by its number of lines; with COUNT(*) I would have counted lines, not orders. That is the aggregate-side face of the fan-out problem from post 09 of this series.
GROUPING SETS lets you list exactly the combinations you want. To get counts by country and by status, like two separate reports, in one result:
SELECT CASE WHEN GROUPING(c.country_code) = 1 THEN 'ALL' ELSE c.country_code END AS country,
CASE WHEN GROUPING(o.status) = 1 THEN 'ALL' ELSE o.status END AS status,
COUNT(*) AS orders
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id
GROUP BY GROUPING SETS ((c.country_code), (o.status), ())
ORDER BY GROUPING(c.country_code), GROUPING(o.status), country, status;country status orders
------- --------- ------
DE ALL 94
GB ALL 45
NL ALL 6
TR ALL 234
ALL Cancelled 24
ALL Delivered 345
ALL Paid 1
ALL Pending 1
ALL Shipped 8
ALL ALL 379CUBE (a, b) produces every combination: (a, b), (a), (b), (). For country and status, CUBE returned 22 rows. The rule is simple: ROLLUP for hierarchical subtotals (year → month → day), CUBE for cross tabulation, GROUPING SETS for the special combinations neither covers.
Production Notes
GROUP BY does not guarantee order. SQL Server used to implement grouping with a sort most of the time, so results often came back sorted, and that habit lives on in many reports. The moment the optimizer picks a hash aggregate, or the plan goes parallel, the order breaks. If you want order, write ORDER BY; it is not free, but it is correct.
Grouping by a formatted string breaks both ordering and performance. This query, grouping by a month label, sorted alphabetically:
SELECT FORMAT(ordered_at, 'MMM yyyy', 'en-US') AS month_label, COUNT(*) AS orders
FROM dbo.orders
WHERE ordered_at >= '2025-01-01' AND ordered_at < '2025-07-01'
GROUP BY FORMAT(ordered_at, 'MMM yyyy', 'en-US')
ORDER BY month_label;month_label orders
----------- ------
Apr 2025 17
Feb 2025 15
Jan 2025 13
Jun 2025 20
Mar 2025 28
May 2025 16April came before February. On top of that, FORMAT runs on the .NET CLR (Common Language Runtime) and has a noticeable per-row cost. Group by a real date with DATEFROMPARTS or DATETRUNC(month, ...) (SQL Server 2022+), and produce the label last, in the presentation layer.
On large tables the cost of grouping comes from two places: how many rows are read, and whether grouping is a sort or a hash. Narrowing the date range with WHERE, letting an index that starts with the GROUP BY columns remove the sort, and reducing the number of COUNT(DISTINCT ...) expressions (each one is a separate aggregation step) are the first three things to look at. The index and execution plan side belongs to the SQL Server Derinlemesine (SQL Server in Depth) series, not this one; there we read the plans of the same queries operator by operator.
In queries with joins, check the row count before aggregating. In Bookstore, 68 orders have two payments; join order_items and payments in the same query and take SUM(amount), and those orders' amounts get multiplied by their number of lines. The safe route is to aggregate each table at its own grain first (one row per order) and then join.
Common Pitfalls
Calling AVG on an integer column and assuming the result is a decimal. In SQL Server int goes in and int comes out; widen the type first.
Treating COUNT(column) and COUNT(*) as synonyms. On a column that can hold NULL they count differently; if you are asking "how many rows", write COUNT(*).
Assuming SUM returns 0 for an empty group. It returns NULL; read it as nullable in the application or use COALESCE.
Using HAVING instead of WHERE. It works, but unnecessary rows get grouped and your intent gets blurry.
Taking "number of orders" with COUNT(*) after a join. If the join multiplied rows, you count lines, not orders; use COUNT(DISTINCT order_id) or aggregate-then-join.
Putting an extra column into the source of a PIVOT and not understanding why rows split. PIVOT implicitly groups by every column you do not aggregate.
A Small Experiment
★☆☆ Return the review count and the correctly scaled average rating (two decimals) for each category. Categories without any reviews should appear with 0 reviews. Hint: LEFT JOIN and COUNT(r.review_id).
★★☆ For 2025, return month by month the number of "delivered", "cancelled" and "other" orders plus the revenue of delivered orders in a single query. Then try writing the same report with PIVOT and note where it gets hard.
★★★ Use ROLLUP to produce order counts over a year → month → day hierarchy, but show only the (year, month) subtotals and the grand total; hide the day detail. Write the same result with GROUPING SETS and compare the number of rows the two queries produce.
Grouping answers "how do I summarise this set?". In the next post we turn the question around: using the result of one query inside another. Subqueries, EXISTS and the famous empty result NOT IN gives with NULL, and then combining sets with UNION, INTERSECT and EXCEPT.