Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL GROUP BY and HAVING: Aggregation Functions, Filters, and Common Pitfalls

The evaluation order that decides WHERE versus HAVING, why COUNT(*) and COUNT(col) disagree on left joins, and how ROLLUP subtotals hide in NULL.

~5 min readSQL
#SQL#Aggregation#Queries
3D illustration of raw row cubes funneled into aggregated category bins with a selective HAVING sieve barrier

Quick Summary

GROUP BY collapses rows into one row per distinct key combination, and the aggregate functions decide what survives the collapse. HAVING filters those groups after aggregation, which is why it is the only place you can reference an aggregate in a condition. The order of evaluation is fixed: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

The four traps are all about that order. Filtering individual rows in HAVING instead of WHERE makes the query slow and sometimes wrong. Selecting an ungrouped column works in MySQL and SQLite by accident and errors on PostgreSQL. COUNT(col) and COUNT(*) differ for exactly the rows you care about. And GROUPING SETS, ROLLUP, and CUBE produce NULL subtotals that look like missing data until you use GROUPING() to tell them apart.

Basic syntax and query signatures

sql
SELECT status, COUNT(*) AS orders, COUNT(coupon_code) AS with_coupon, SUM(total) AS revenue, AVG(total) AS avg_order FROM orders WHERE placed_at >= DATE '2026-01-01' GROUP BY status HAVING SUM(total) > 10000 ORDER BY revenue DESC;

Grouping expressions may be columns, expressions, or positions in some dialects, but naming the expression is clearer and portable. GROUP BY 1 is handy interactively and hostile in code review because adding a column silently changes the grouping.

sql
-- Multi-level subtotals in one pass SELECT region, product, SUM(amount) AS revenue, GROUPING(region) AS is_region_subtotal, GROUPING(product) AS is_product_subtotal FROM sales GROUP BY ROLLUP (region, product) ORDER BY region NULLS LAST, product NULLS LAST;

ROLLUP (a, b) is shorthand for GROUPING SETS ((a, b), (a), ()) — the detail, the per-a subtotal, and the grand total. CUBE (a, b) adds (b) as well. Both are supported by PostgreSQL, SQL Server, Oracle, MySQL 8, BigQuery, Snowflake, and DuckDB.

Key comparison table

ClauseRuns onSees rows or groupsCan use aggregatesFilters what
WHEREBefore groupingIndividual rowsNoInput rows
HAVINGAfter groupingGroupsYesOutput groups
QUALIFYAfter window functionsRows with window valuesWindow functions onlyWindow results
ORDER BYLastOutputYes, if groupedOrdering only
LIMITAfter orderingOutputNoRow count
AggregateIgnores NULLReturns on empty groupCommon surprise
COUNT(*)No — counts rows0Counts rows with all-NULL columns
COUNT(col)Yes0Drops NULL rows silently
COUNT(DISTINCT col)Yes0Cannot be combined with a second DISTINCT
SUM(col)YesNULLNot 0 — wrap in COALESCE
AVG(col)YesNULLDenominator excludes NULL, so averages rise
MIN/MAXYesNULLOn text, uses collation order

Practical edge cases and deep dives

1. WHERE versus HAVING is a performance question with a correctness edge

sql
-- Slow: aggregates every order ever, then discards most groups SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING MIN(placed_at) >= DATE '2026-01-01'; -- Fast, but a different question: counts only 2026 orders SELECT customer_id, COUNT(*) FROM orders WHERE placed_at >= DATE '2026-01-01' GROUP BY customer_id;

These return different numbers. The first counts all orders for customers who became active in 2026. The second counts 2026 orders for anyone who has any. Neither is wrong; mixing them up is. When the intent really is the first, keep HAVING and accept the scan — or restructure with a CTE so the row filter and the group filter are named separately.

HAVING on a non-aggregate column is legal in most engines and almost always a mistake: HAVING status = 'paid' after GROUP BY status is equivalent to WHERE status = 'paid', but it reads as if it were filtering groups.

2. Selecting a column that is not grouped

sql
-- PostgreSQL: ERROR 42803 — column "o.customer_name" must appear in the GROUP BY clause SELECT o.customer_name, SUM(o.total) FROM orders o GROUP BY o.customer_id;

PostgreSQL and standard SQL require every selected column to be grouped or aggregated. MySQL with ONLY_FULL_GROUP_BY disabled (and SQLite by default) will return an arbitrary row's value — a value that changes when the plan changes. MySQL 8 enables ONLY_FULL_GROUP_BY by default, and the error it raises (Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column) is the fix, not the obstacle.

Functional dependency saves the common case: group by the primary key and every other column of that table becomes selectable, because the key determines the row. That works in PostgreSQL when the key is a declared primary key, and in MySQL with ONLY_FULL_GROUP_BY for a unique NOT NULL key. Grouping by customer_id while selecting customer_name is not that case — add the name to GROUP BY, or aggregate it (MIN(name)), or join a customer table that has one row per id.

3. COUNT(*), COUNT(col), and the fan-out double count

sql
SELECT c.id, COUNT(DISTINCT o.id) AS orders, COUNT(DISTINCT o.coupon_code) AS coupons_used FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id;

COUNT(DISTINCT ...) is the blunt fix for fan-out. It hides the join multiplication instead of removing it, and it costs a sort or hash per distinct expression. The structural fix is to aggregate before joining:

sql
WITH per_customer AS ( SELECT customer_id, COUNT(*) AS orders, COUNT(coupon_code) AS coupons_used FROM orders GROUP BY customer_id ) SELECT c.id, COALESCE(p.orders, 0), COALESCE(p.coupons_used, 0) FROM customers c LEFT JOIN per_customer p ON p.customer_id = c.id;

Also note the COALESCE: a LEFT JOIN group with no matching rows produces COUNT(*) = 1 (the padded row) but COUNT(o.id) = 0. Mixing the two in one report is the single most common source of "why does this customer show one order".

4. ROLLUP/CUBE NULLs versus real NULLs

sql
SELECT COALESCE(region, 'All regions') AS region_label, SUM(amount) AS revenue FROM sales GROUP BY ROLLUP (region);

This is wrong the moment region itself can be NULL for unassigned sales: both the grand total and the "unassigned" group render as "All regions". GROUPING() is the discriminator, because it returns 1 for a subtotal row and 0 for a real group:

sql
SELECT CASE WHEN GROUPING(region) = 1 THEN 'All regions' ELSE COALESCE(region, 'Unassigned') END AS region_label, SUM(amount) AS revenue FROM sales GROUP BY ROLLUP (region);

The same distinction matters downstream: a HAVING region IS NULL filter on a ROLLUP result selects the grand total, not the unassigned rows.

5. Grouping by time without losing the index

sql
-- Index on placed_at is unusable: the column is wrapped SELECT date_trunc('day', placed_at) AS day, COUNT(*) FROM orders GROUP BY date_trunc('day', placed_at); -- Still correct, but scan-bound; for large tables use a generated column ALTER TABLE orders ADD COLUMN placed_day date GENERATED ALWAYS AS (placed_at::date) STORED; CREATE INDEX ON orders (placed_day); SELECT placed_day, COUNT(*) FROM orders GROUP BY placed_day;

Grouping by an expression is fine functionally; the index question is separate. A stored generated column plus an index gives an index-only scan for the daily rollup. On PostgreSQL, date_trunc('day', ts) on a timestamptz also depends on the session time zone — the same rows can land in different days for different clients, which is a reporting bug that only appears when someone in another region runs the query.

Common mistakes and runtime errors

Error or symptomCauseFix
column "x" must appear in the GROUP BY clause or be used in an aggregate function (PG 42803)Ungrouped column in SELECTGroup it, aggregate it, or join a one-row-per-key table
Expression #1 of SELECT list is not in GROUP BY clause… (MySQL 1055)ONLY_FULL_GROUP_BY enabledSame fix; do not disable the mode
In aggregated query without GROUP BY, expression #1… contains nonaggregated columnAggregate and bare column with no GROUP BYAdd GROUP BY, or remove the bare column
aggregate functions are not allowed in WHERE (PG 42803)Filtering on COUNT/SUM before groupingMove the condition to HAVING
Every count is a multiple of the real numberJoin fan-out before groupingAggregate first, or COUNT(DISTINCT ...)
SUM returns NULL for an empty periodNo rows, and SUM returns NULL not 0COALESCE(SUM(x), 0)
Averages look too highAVG(col) skipped NULL rowsAVG(COALESCE(col, 0)) if zeros belong in the denominator
column must appear in GROUP BY on SELECT *Selecting everything while groupingList the columns explicitly
GROUPING/ROLLUP not recognisedOlder MySQL (5.7) or SQLite build without the featureEmulate with UNION ALL of detail and totals
Totals double after adding a LEFT JOINJoined table has multiple rows per keyAggregate the joined table in a CTE first

One habit prevents most of these: run the grouped query without the aggregates first, look at the rows, and confirm the grain is what you think it is. If SELECT customer_id, status FROM orders returns two rows per customer, then GROUP BY customer_id is losing information you probably wanted in the output.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.