Grouping in Practice

The clauses are simple. Combining them into the reports people actually ask for is the skill, and it comes down to a handful of patterns.

Conditional aggregation — the pattern worth memorising

You often want several different counts in one row. CASE inside an aggregate does it, and it removes the need for repeated queries or self-joins:

SQL
SELECT COUNT(*)                                            AS all_orders,
       SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

CASE produces 1 or 0 per row, and SUM adds them up. COUNT(CASE WHEN … THEN 1 END) works too — with no ELSE, non-matching rows become NULL and COUNT skips them. PostgreSQL also offers COUNT(*) FILTER (WHERE …), which is cleaner but not portable.

The same trick pivots a column into a row:

SQL
SELECT dept_id,
       COUNT(*)                                          AS staff,
       SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END)       AS active,
       SUM(CASE WHEN salary IS NULL THEN 1 ELSE 0 END)   AS salary_missing
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

Percentages of the whole

Dividing a group's count by the total needs the total available inside every row. A scalar subquery is the portable way:

SQL
SELECT status,
       COUNT(*) AS orders,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM orders), 1) AS pct
FROM orders
GROUP BY status
ORDER BY orders DESC;

Two details matter. 100.0 rather than 100 forces floating-point division — with two integers, PostgreSQL and SQL Server do integer division and every percentage under 100% comes out as 0. And this is a job window functions do better (COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()), which the advanced module covers.

Ranking groups

GROUP BY with ORDER BY and LIMIT answers "top N by category":

SQL
SELECT customer_id,
       COUNT(*)              AS orders,
       ROUND(SUM(amount), 2) AS spent
FROM orders
GROUP BY customer_id
ORDER BY spent DESC
LIMIT 3;

Look at the third row before trusting it. That customer placed the most orders of anyone and still comes last of the three, because one of them is a refund stored as a negative amount and SUM duly subtracts it. Whether a refund should cancel out a sale depends on the question being asked, and the query has quietly answered it for you.

Raise the LIMIT to 5 and something else appears: a customer_id with no matching row in customers at all. orders was never joined to anything, so nothing was there to notice. Aggregates faithfully total whatever you hand them, orphaned rows included.

A data-quality check you can run anywhere

SQL
SELECT COUNT(*)                                        AS rows_total,
       COUNT(email)                                    AS has_email,
       COUNT(*) - COUNT(email)                         AS missing_email,
       COUNT(DISTINCT lower(name))                     AS distinct_names,
       COUNT(*) - COUNT(DISTINCT lower(name))          AS probable_duplicates
FROM customers;

Six rows, one missing email, one duplicate name differing only in case. Running this shape of query against a new table before writing anything else will save you more time than any other habit in this course.

Multiple grouping levels

SQL
SELECT substr(placed_at, 1, 7) AS month,
       status,
       COUNT(*)                AS orders,
       ROUND(SUM(amount), 2)   AS revenue
FROM orders
GROUP BY substr(placed_at, 1, 7), status
ORDER BY month, status;

Month × status. Reading it as a grid is what a spreadsheet pivot does — and GROUPING SETS, ROLLUP and CUBE (PostgreSQL, SQL Server, Oracle; MySQL has WITH ROLLUP) add subtotal rows automatically. They are worth knowing exist.

Common mistakes

Integer division in percentages — multiply by 100.0.
Aggregating an unjoined table — the totals include orphaned rows.
COUNT(CASE WHEN … THEN 1 ELSE 0 END) — COUNT counts the zeros too. Use SUM, or drop the ELSE.
Assuming a refund is excluded — negative amounts are summed like any other.

Interview question

Count total orders and shipped orders in a single query, without a subquery.

SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) beside COUNT(*). Conditional aggregation is the answer to a whole family of interview questions and it is worth being fluent in it.

Module complete

You can now aggregate, group, filter groups, and shape the results into a report. Next: joins — combining tables — where every NULL rule from this module returns with more force, because a LEFT JOIN manufactures NULLs of its own.