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.

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
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.
-- 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
| Clause | Runs on | Sees rows or groups | Can use aggregates | Filters what |
|---|---|---|---|---|
WHERE | Before grouping | Individual rows | No | Input rows |
HAVING | After grouping | Groups | Yes | Output groups |
QUALIFY | After window functions | Rows with window values | Window functions only | Window results |
ORDER BY | Last | Output | Yes, if grouped | Ordering only |
LIMIT | After ordering | Output | No | Row count |
| Aggregate | Ignores NULL | Returns on empty group | Common surprise |
|---|---|---|---|
COUNT(*) | No — counts rows | 0 | Counts rows with all-NULL columns |
COUNT(col) | Yes | 0 | Drops NULL rows silently |
COUNT(DISTINCT col) | Yes | 0 | Cannot be combined with a second DISTINCT |
SUM(col) | Yes | NULL | Not 0 — wrap in COALESCE |
AVG(col) | Yes | NULL | Denominator excludes NULL, so averages rise |
MIN/MAX | Yes | NULL | On text, uses collation order |
Practical edge cases and deep dives
1. WHERE versus HAVING is a performance question with a correctness edge
-- 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
-- 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
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:
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
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:
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
-- 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 symptom | Cause | Fix |
|---|---|---|
column "x" must appear in the GROUP BY clause or be used in an aggregate function (PG 42803) | Ungrouped column in SELECT | Group 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 enabled | Same fix; do not disable the mode |
In aggregated query without GROUP BY, expression #1… contains nonaggregated column | Aggregate and bare column with no GROUP BY | Add GROUP BY, or remove the bare column |
aggregate functions are not allowed in WHERE (PG 42803) | Filtering on COUNT/SUM before grouping | Move the condition to HAVING |
| Every count is a multiple of the real number | Join fan-out before grouping | Aggregate first, or COUNT(DISTINCT ...) |
SUM returns NULL for an empty period | No rows, and SUM returns NULL not 0 | COALESCE(SUM(x), 0) |
| Averages look too high | AVG(col) skipped NULL rows | AVG(COALESCE(col, 0)) if zeros belong in the denominator |
column must appear in GROUP BY on SELECT * | Selecting everything while grouping | List the columns explicitly |
GROUPING/ROLLUP not recognised | Older MySQL (5.7) or SQLite build without the feature | Emulate with UNION ALL of detail and totals |
Totals double after adding a LEFT JOIN | Joined table has multiple rows per key | Aggregate 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 toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.