Week 6: Aggregation, Grouping & Summarizing Data in SQL

1. GROUP BY With Multiple Columns

This week extends Week 5's orders table with a category column, alongside the same customers table:

order_id | customer_id | category    | amount | order_date
---------+-------------+-------------+--------+------------
1        | 1           | Electronics | 1200   | 2026-01-04
2        | 2           | Clothing    | 450    | 2026-01-05
3        | 1           | Electronics | 890    | 2026-01-06
4        | 1           | Clothing    | 300    | 2026-01-09
5        | 4           | Electronics | NULL   | 2026-01-10
6        | 3           | Clothing    | 700    | 2026-02-02
7        | 2           | Electronics | 1500   | 2026-02-03

A single GROUP BY region collapses everything down to one row per region. Listing more than one column groups by every unique combination of them instead — a finer-grained summary, without changing anything else about the query:

SELECT c.region, o.category, SUM(o.amount) AS total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.region, o.category;

-- One row per (region, category) PAIR that actually appears in the data --
-- e.g. North/Electronics and North/Clothing show up as two separate rows,
-- not blended into one "North" total

This is exactly the same idea as dragging a second field into a PivotTable's Rows zone in Week 3 — each additional GROUP BY column adds another level of detail to what counts as "the same group," and only combinations that actually exist in the data produce a row.

2. HAVING, Revisited

Week 2 covered HAVING with a single condition. It combines with AND/OR exactly like WHERE does, and — the detail worth re-emphasizing — it can only reference columns that are either grouped on or wrapped in an aggregate function:

SELECT c.region, o.category, SUM(o.amount) AS total_amount, COUNT(*) AS order_count
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.region, o.category
HAVING SUM(o.amount) > 1000 AND COUNT(*) > 1;

-- Only (region, category) groups with BOTH a total over 1000 AND more than
-- one order survive -- a single big order no longer qualifies on its own

Requiring both conditions together is a common real pattern: "significant total and repeat behavior" filters out a one-off large purchase that happens to clear the revenue bar but doesn't actually represent a meaningful trend.

3. CASE WHEN for Conditional Aggregation

CASE WHEN is SQL's inline if/else — and paired with an aggregate function, it's the standard way to turn category values into their own columns, without a dedicated PIVOT keyword:

SELECT
  c.region,
  SUM(CASE WHEN o.category = 'Electronics' THEN o.amount ELSE 0 END) AS electronics_total,
  SUM(CASE WHEN o.category = 'Clothing'    THEN o.amount ELSE 0 END) AS clothing_total,
  SUM(o.amount) AS grand_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.region;

-- One row per region, but Electronics and Clothing spend are now SEPARATE
-- COLUMNS instead of separate rows -- exactly the shape a chart or report usually wants

Read the pattern as: for each row, check the condition; if true, count o.amount toward this column's sum, otherwise count 0 (contributing nothing). Repeating that per category, each wrapped in its own SUM, produces one column per category — all computed in a single pass over the data, in a single query.

4. Combining Aggregates in One Query

Multiple aggregate functions, including COUNT(DISTINCT ...), can all sit side by side in the same SELECT — each one summarizes independently over whatever rows made it into the current group:

SELECT
  c.region,
  COUNT(*) AS order_count,
  COUNT(DISTINCT o.customer_id) AS unique_customers,
  ROUND(AVG(o.amount), 2) AS avg_order_value,
  SUM(o.amount) AS total_revenue
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.region;

COUNT(*) counts rows; COUNT(DISTINCT o.customer_id) counts unique customers — a very different number the moment any customer has placed more than one order, and the difference between the two is itself a useful metric ("orders per customer"). ROUND(..., 2) is worth reaching for on any AVG headed for a report — raw floating-point division rarely lands on a clean two decimal places on its own.

5. Writing Readable Queries

A query that returns the right answer but takes five minutes for a teammate to parse is still a cost — a few habits keep that cost low:

-- 1. One clause per line, consistently indented -- scan the shape, not the words
SELECT
  c.region,
  SUM(o.amount) AS total_revenue
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.region
HAVING SUM(o.amount) > 500
ORDER BY total_revenue DESC;

-- 2. Always alias an aggregate -- "SUM(amount)" as a raw column header helps no one
SELECT SUM(amount) AS total_revenue FROM orders;   -- do this
SELECT SUM(amount) FROM orders;                     -- not this

-- 3. WITH (a Common Table Expression, or CTE) names an intermediate result --
-- a preview of Week 7's main topic, but useful for readability starting now
WITH regional_totals AS (
  SELECT c.region, SUM(o.amount) AS total_amount
  FROM orders o
  INNER JOIN customers c ON o.customer_id = c.customer_id
  GROUP BY c.region
)
SELECT * FROM regional_totals WHERE total_amount > 1000;

None of these change what a query returns — only how quickly someone else (including you, in six months) can understand it. A named CTE in particular turns "one long, nested query" into "a sequence of clearly labeled steps," which is exactly the readability upgrade Week 7 builds on directly.

6. Hands-on Exercise

7. Knowledge Check

Four quick questions. Expand each to check your answer.