GROUP BY and Aggregation

TL;DR: GROUP BY collapses rows into one per distinct key, and every selected column must then be either in the GROUP BY or wrapped in an aggregate, because a group of many rows has no single value for an ungrouped column. WHERE filters individual rows before grouping; HAVING filters whole groups after. Watch two traps: COUNT ignores NULLs (so COUNT(col) < COUNT(*)), and conditional aggregation (SUM(CASE WHEN ... THEN 1 ELSE 0 END)) is how you pivot and count subsets in one pass.

Aggregate context vs scalar context

A non-grouped query like SELECT name FROM users runs in scalar context: one output row per input row, each column has a concrete value. The moment you add GROUP BY (or use an aggregate with no GROUP BY), the query switches to aggregate context: the engine partitions rows into groups and emits one row per group. The whole select list now has to answer the question "what is the single value for this group?"

That is why SELECT customer_id, amount FROM orders GROUP BY customer_id is an error. The group "customer 7" has many orders with different amount values; there is no one amount to show. You must either group by it too or aggregate it (SUM(amount), MAX(amount)). Strict engines (Postgres, SQL Server, BigQuery) reject the ungrouped column outright. MySQL historically returned an arbitrary value for it, a well-known footgun now off by default under ONLY_FULL_GROUP_BY.

WHERE vs HAVING

They filter at different stages, and the order matters for both correctness and speed.

WHERE runs before grouping, on individual rows, and cannot see aggregates. HAVING runs after, on the computed group values. So "only paid orders" is a WHERE (a per-row condition), but "only customers with more than 5 orders" is a HAVING COUNT(*) > 5 (a per-group condition). Put a row-level filter in WHERE, not HAVING: filtering rows early shrinks what has to be grouped, which is usually faster. A condition that needs an aggregate has no choice but HAVING.

COUNT and the NULL trap

The three COUNT forms are not interchangeable:

So COUNT(email) understates row count whenever emails are missing, and people read it as total customers and report a number that is too low. Other aggregates also skip NULLs: AVG(score) divides by the count of non-NULL scores, not by the row count, so missing scores quietly raise the average. If you want missing values treated as zero, do it explicitly with AVG(COALESCE(score, 0)). Know which behavior you want and write it down.

Conditional aggregation

The most useful pattern that candidates forget: push a CASE inside an aggregate to count or sum a subset, producing several metrics in one scan.

SELECT
  customer_id,
  COUNT(*)                                            AS total_orders,
  SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END) AS paid_revenue,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END)     AS refund_count,
  COUNT(*) FILTER (WHERE status = 'paid')             AS paid_orders  -- Postgres
FROM orders
GROUP BY customer_id;

This pivots status into columns without a self-join and without scanning the table three times. The SUM(CASE ...) idiom is portable everywhere; the FILTER (WHERE ...) clause is the cleaner standard-SQL form supported by Postgres. Both beat running three separate filtered queries and joining the results.

Why interviewers probe this

GROUP BY questions catch people who memorized syntax but never reasoned about set semantics. The interviewer screens for whether you know why an ungrouped, unaggregated column is an error (a group has no single value for it) rather than just knowing the rule. The strong move is to state the WHERE-then-GROUP-then-HAVING pipeline and reach for conditional aggregation when asked for several metrics by category. The held-back follow-up is usually "why is COUNT(col) lower than COUNT(*)?" or "compute the paid rate per customer in one query," which separates the people who default to SUM(CASE) from those who write three queries and a join.

Common misconceptions

• "You can select any column alongside an aggregate." Only grouped columns or other aggregates. A bare ungrouped column is an error (or an arbitrary value in lax MySQL).
• "HAVING is just WHERE for grouped queries." HAVING filters groups after aggregation and can reference aggregates; WHERE filters rows before and cannot.
• "COUNT(col) counts the rows." It counts non-NULL values of that column. Use COUNT(*) for rows.
• "You need separate queries or a pivot tool to count subsets." SUM(CASE WHEN ...) or COUNT(*) FILTER (WHERE ...) does it in a single pass.

Key takeaways

• GROUP BY emits one row per distinct key; every output column must be grouped or aggregated.
• WHERE filters rows before grouping, HAVING filters groups after; prefer WHERE for row-level conditions.
• COUNT(*) includes NULLs, COUNT(col) does not, and AVG/SUM also skip NULLs; COALESCE if you want them as zero.
• Conditional aggregation with SUM(CASE ...) or FILTER (WHERE ...) computes per-category metrics in one scan.