Grouping and aggregation, without double counting

GROUP BY collapses many rows into one row per distinct combination of the grouping expressions. Everything else about it follows from that sentence — including the errors.

SELECT country, COUNT(*) AS customers
FROM customer
GROUP BY country;

One row per country. Which means: once you group, a single output row corresponds to many input rows, so the only things you can select are the grouping expressions themselves and aggregates over the rest. There is no sensible answer to “which email?” when the group has 400 of them.

The clause evaluation order

Almost every confusing aggregation question is answered by knowing this order:

FROM  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY  →  LIMIT

Consequences worth naming:

• WHERE runs before grouping, so it filters rows and cannot see aggregates.
• HAVING runs after grouping, so it filters groups and can see aggregates.
• SELECT runs after both, which is why an alias defined in SELECT is not available in WHERE.
• ORDER BY runs last, which is why an alias is available there.

SELECT country, COUNT(*) AS customers
FROM customer
WHERE created_at >= '2026-01-01'     -- rows: before grouping
GROUP BY country
HAVING COUNT(*) >= 10                -- groups: after grouping
ORDER BY customers DESC;             -- alias is fine here

Putting COUNT(*) >= 10 in WHERE is an error in both engines. Putting created_at >= '2026-01-01' in HAVING sometimes works and is always wrong: you have filtered after aggregating, so the counts were computed over rows you meant to exclude.

Alias visibility is one of the few places MySQL is more permissive:

So HAVING customers >= 10 runs on MySQL and fails on PostgreSQL. Write the full expression and it works on both.

ONLY_FULL_GROUP_BY

PostgreSQL has always rejected selecting an ungrouped, unaggregated column:

-- PostgreSQL: ERROR, column "customer.email" must appear in the GROUP BY clause
SELECT country, email, COUNT(*) FROM customer GROUP BY country;

MySQL historically accepted it and returned an arbitrary email from each group — no error, no warning, a value that could change between runs. Since 5.7 the ONLY_FULL_GROUP_BY mode is on by default and MySQL rejects it too, but the mode is a server setting, so a legacy or misconfigured server still accepts it.

Check before you trust it:

SELECT @@sql_mode;   -- MySQL: look for ONLY_FULL_GROUP_BY

If you genuinely want “one representative row per group”, say which one. That is a window function, not a GROUP BY — lesson 6.

MySQL does offer ANY_VALUE(col) to state explicitly “I know this is arbitrary”. It is honest documentation of a compromise; it is not a fix.

Conditional aggregation

Counting subsets in one pass is the most useful aggregation technique there is, and the dialects differ.

-- PostgreSQL: the FILTER clause
SELECT
  COUNT(*)                                  AS orders,
  COUNT(*) FILTER (WHERE status = 'paid')   AS paid,
  COUNT(*) FILTER (WHERE shipped_at IS NULL) AS unshipped,
  SUM(unit_cents) FILTER (WHERE quantity > 1) AS bulk_cents
FROM "order" o JOIN order_item i ON i.order_id = o.id;

-- MySQL: SUM over a boolean, which works everywhere
SELECT
  COUNT(*)                                       AS orders,
  SUM(status = 'paid')                           AS paid,
  SUM(shipped_at IS NULL)                        AS unshipped,
  SUM(CASE WHEN quantity > 1 THEN unit_cents END) AS bulk_cents
FROM `order` o JOIN order_item i ON i.order_id = o.id;

SUM(boolean) works in MySQL because a boolean is 1 or 0 there. In PostgreSQL a boolean is not a number, so the portable form is SUM(CASE WHEN cond THEN 1 ELSE 0 END) or COUNT(*) FILTER (...).

Note the CASE with no ELSE in the last line — deliberate here. Rows that fail the condition produce NULL, and SUM ignores NULL, so the total is over matching rows only. This is the one place the missing ELSE from lesson 2 is the right choice, and it is worth a comment in real code.

COUNT variants

SELECT
  COUNT(*)                    AS rows_seen,
  COUNT(coupon_code)          AS with_coupon,      -- non-NULL only
  COUNT(DISTINCT coupon_code) AS distinct_coupons  -- non-NULL, deduplicated
FROM "order";

COUNT(DISTINCT ...) is much more expensive than COUNT(*) — it has to deduplicate, which means sorting or hashing the values. On a large table it is often the single slowest part of a dashboard query.

Aggregating strings

The MySQL truncation is the trap: GROUP_CONCAT silently cuts the result at group_concat_max_len and only raises a warning, which most clients discard. A report that looks fine on small groups quietly loses data on large ones.

The bug that matters: join fan-out

This is the most common wrong-number-in-a-report bug in SQL, and it is not a NULL problem or a syntax problem. It is arithmetic.

An order has one shipping fee and many items. Join them and the order row is repeated once per item:

-- WRONG: shipping is counted once per item
SELECT o.id, SUM(i.unit_cents * i.quantity) AS goods, SUM(o.shipping_cents) AS shipping
FROM "order" o
JOIN order_item i ON i.order_id = o.id
GROUP BY o.id;

An order with three items reports three times the shipping. COUNT(*) has the same problem: it counts item rows, not orders.

Two correct shapes:

-- 1. Aggregate first, then join. One row per order on each side.
SELECT o.id, o.shipping_cents, g.goods
FROM "order" o
JOIN (
  SELECT order_id, SUM(unit_cents * quantity) AS goods
  FROM order_item
  GROUP BY order_id
) g ON g.order_id = o.id;

-- 2. Keep the join, but make each aggregate count the right thing
SELECT o.id,
       SUM(i.unit_cents * i.quantity) AS goods,
       MIN(o.shipping_cents)          AS shipping,   -- constant within the group
       COUNT(DISTINCT o.id)           AS orders
FROM "order" o
JOIN order_item i ON i.order_id = o.id
GROUP BY o.id;

Option 1 is clearer and usually faster. Option 2 works because shipping_cents is constant inside a group, so MIN picks it without distorting anything — but it relies on the reader knowing that, which is why the comment is there.

The general rule: if a query joins one-to-many and then aggregates, check whether each aggregate is summing over the right grain. Adding DISTINCT to make the number look right usually hides the fan-out rather than fixing it.

Rollups and subtotals

-- PostgreSQL: full support
SELECT country, status, COUNT(*)
FROM "order" o JOIN customer c ON c.id = o.customer_id
GROUP BY GROUPING SETS ((country, status), (country), ());

-- Also: ROLLUP (country, status), CUBE (country, status)

-- MySQL: ROLLUP only, and written as a modifier
SELECT country, status, COUNT(*)
FROM `order` o JOIN customer c ON c.id = o.customer_id
GROUP BY country, status WITH ROLLUP;

Subtotal rows carry NULL in the columns they aggregate over, which is indistinguishable from a real NULL in the data. GROUPING(country) returns 1 for a subtotal row in both engines — use it rather than guessing.

What to take away

• FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Most confusion dissolves against that list.
• WHERE filters rows, HAVING filters groups. They are not interchangeable.
• Write the full expression in HAVING, not the alias, so it runs on both engines.
• Conditional aggregation: FILTER in PostgreSQL, SUM(CASE WHEN ...) everywhere.
• GROUP_CONCAT truncates at 1024 bytes by default.
• Joining one-to-many before aggregating double counts. Aggregate first.