GROUP BY and HAVING

The rule

Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate. Postgres and SQL Server enforce this; MySQL historically allowed violations and returned an arbitrary row, which is worse than an error because it looks like it worked.

SELECT region, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY region;

WHERE vs HAVING

WHERE filters rows before grouping. HAVING filters groups after aggregation.

SELECT region, SUM(total) AS revenue
FROM orders
WHERE order_date >= '2024-01-01'   -- rows first
GROUP BY region
HAVING SUM(total) > 100000;        -- then groups

You cannot use an aggregate in WHERE — it does not exist yet. Put row-level conditions in WHERE (it is cheaper, fewer rows reach the grouping) and aggregate conditions in HAVING.

Counting correctly

COUNT(*) counts rows. COUNT(col) counts non-null values of that column. COUNT(DISTINCT col) counts distinct non-null values.

That difference is a frequent interview question and a frequent bug. If a column is nullable, COUNT(col) and COUNT(*) will disagree — which is sometimes exactly what you want, and sometimes a silent error.

SUM and AVG also skip NULLs, so AVG divides by non-null count. If NULL should count as zero, wrap it: AVG(COALESCE(col, 0)).

Conditional aggregation

Rather than several queries, aggregate with a CASE inside:

SELECT region,
       COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
       SUM(CASE WHEN status = 'refunded' THEN total ELSE 0 END) AS refunded_value
FROM orders GROUP BY region;

This pivot-in-SQL pattern comes up constantly in real analyst work and is worth being fluent in. Note COUNT(CASE … THEN 1 END) with no ELSE — the NULL from the missing ELSE is what makes the count skip non-matching rows.