S038 · Aggregations — GROUP BY, HAVING, Subqueries

Module M04: Databases & SQL · Session 38 of 130 · Track: DE ∑

What you'll be able to do after this session

• Explain Aggregations to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S037 — Joins — INNER, LEFT, RIGHT, FULL, Anti-Join

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · SQL GROUP BY & Aggregates — Alex The Analyst — 10 min, walks through a real sales-report example.
• 🎥 Deep dive (30–60 min) · Stanford — SQL Aggregation & GROUP BY (Jennifer Widom) — 40 min from the canonical Stanford SQL course.
• 🎥 Hands-on demo (10–20 min) · Mode Analytics — GROUP BY tutorial video — types real aggregate queries; shows HAVING vs WHERE trap.
• 📖 Canonical article · PostgreSQL Docs — Aggregate Functions & GROUP BY — reference-grade with worked examples.
• 📖 Book chapter / tutorial · Mode Analytics — SQL Aggregate Functions — interactive tutorial you can run in-browser.
• 📖 Engineering blog · Uber Eng — Analytical Query Federation with Presto — real-world aggregation at Uber scale (petabytes).

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: Turn rows into insight.

Raw tables are just facts — one row per event, one per order, one per user. Nobody wants to read a million rows. They want summaries: "how much revenue by month," "top 5 products by sales," "average session length per country." Aggregation is the SQL that converts row-level data into these summaries: COUNT, SUM, AVG, MIN, MAX, STRING_AGG — and the GROUP BY clause that decides "aggregate per what."

Think of GROUP BY as "putting rows into buckets," then applying an aggregate function to each bucket. GROUP BY dept says "one bucket per distinct dept"; AVG(salary) says "for each bucket, average the salary column." The output has one row per bucket — you always lose row-level detail after GROUP BY, and that's the whole point.

HAVING filters after aggregation, WHERE filters before. Swapping them is the most common SQL bug on Earth. Subqueries let you use one query's output as another query's input — perfect for "employees earning above the company average."

The forever gotcha: every column in SELECT must either appear in GROUP BY or be inside an aggregate function. If it doesn't, some databases (MySQL) return whatever value they feel like — a silently wrong report. Postgres and Snowflake reject it outright, which is a feature, not a bug.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Reusing employees from S036:

Query 1 — headcount and avg salary per dept:

SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM   employees
GROUP  BY dept
ORDER  BY avg_salary DESC;

Execution flow:

Result:

Query 2 — depts with avg salary > 90k (HAVING):

SELECT dept, AVG(salary) AS avg_salary
FROM   employees
GROUP  BY dept
HAVING AVG(salary) > 90000;

WHERE avg_salary > 90000 would fail — the alias doesn't exist yet at WHERE time.

WHERE vs HAVING — the cheat sheet:

Aggregate function reference:

Subqueries — three common shapes:

1. Scalar subquery — returns one value, usable anywhere:

   copy

   SELECT name, salary FROM employees
   WHERE salary > (SELECT AVG(salary) FROM employees);

2. IN subquery — returns a list, tested against a column:

   copy

   SELECT name FROM employees
   WHERE dept IN (SELECT dept FROM depts WHERE region = 'India');

3. Correlated subquery — inner query references outer row (runs per outer row):

   copy

   SELECT e.name, e.dept, e.salary
   FROM   employees e
   WHERE  e.salary = (SELECT MAX(salary) FROM employees WHERE dept = e.dept);

   Returns top earner per dept. Correlated subqueries can be slow — window functions (S039) are usually faster.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Save as agg_lab.py:

import sqlite3
conn = sqlite3.connect(":memory:"); cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER
);
INSERT INTO employees VALUES
    (1,'Alice','Eng',120000),(2,'Bob','Eng',95000),(3,'Carol','HR',80000),
    (4,'Dan','Eng',140000),(5,'Eve','Sales',110000),(6,'Frank','Sales',60000),
    (7,'Grace','HR',NULL);   -- missing salary
""")
 
def run(label, sql):
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print("  ", row)
 
run("1. Headcount & avg salary per dept", """
    SELECT dept, COUNT(*) AS headcount,
           COUNT(salary) AS with_salary,
           AVG(salary) AS avg_sal
    FROM employees GROUP BY dept ORDER BY avg_sal DESC;""")
 
# Notice HR: COUNT(*)=2 (Carol + Grace), COUNT(salary)=1 (Grace ignored)
 
run("2. HAVING — depts with avg salary > 90k", """
    SELECT dept, AVG(salary) AS avg_sal
    FROM employees GROUP BY dept
    HAVING AVG(salary) > 90000;""")
 
run("3. WHERE (pre-agg) vs HAVING (post-agg) — filter before grouping", """
    SELECT dept, AVG(salary) AS avg_sal
    FROM employees WHERE salary > 70000
    GROUP BY dept
    HAVING COUNT(*) >= 2;""")
 
run("4. Scalar subquery — earn above company average", """
    SELECT name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);""")
 
run("5. Correlated subquery — top earner per dept", """
    SELECT e.name, e.dept, e.salary FROM employees e
    WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE dept = e.dept);""")
 
run("6. COUNT(DISTINCT) — how many distinct depts", """
    SELECT COUNT(DISTINCT dept) AS num_depts FROM employees;""")
 
run("7. STRING_AGG (sqlite: GROUP_CONCAT) — comma list of names per dept", """
    SELECT dept, GROUP_CONCAT(name, ', ') AS names
    FROM employees GROUP BY dept ORDER BY dept;""")
 
# --- CLASSIC TRAP: SUM over empty result is NULL, not 0 ---
run("8. SUM of no rows returns NULL, not 0", """
    SELECT SUM(salary) FROM employees WHERE dept = 'Legal';""")
run("8b. Fix with COALESCE", """
    SELECT COALESCE(SUM(salary), 0) FROM employees WHERE dept = 'Legal';""")
 
# --- CLASSIC TRAP: GROUP BY with SELECT of a non-grouped, non-aggregated column ---
try:
    run("9. Illegal in strict SQL — will error in Postgres, silent in old MySQL", """
        SELECT dept, name, AVG(salary) FROM employees GROUP BY dept;""")
except sqlite3.OperationalError as e:
    print("Rejected:", e)
# SQLite is permissive — it will pick some arbitrary `name` per dept. Try in Postgres, it errors.
 
conn.close()

Checklist — observe when you run:

1. Query 1: HR shows COUNT(*)=2 but COUNT(salary)=1 and avg_sal=80000 (Grace's null ignored). This is the null-handling subtlety.
2. Query 2 returns only Eng (AVG=118k); Sales (85k) and HR (80k) filtered out.
3. Query 3: WHERE salary > 70000 runs first (drops Frank & Grace); HAVING COUNT(*) >= 2 filters groups with at least 2 remaining → only Eng survives.
4. Query 4: Alice, Dan, Eve — all above the 100.8k average.
5. Query 5 returns top earner per dept (Dan/Eng, Carol/HR, Eve/Sales). Correlated subquery runs 6 times — expensive on big tables; a window function (S039) does it in one pass.
6. Query 7: GROUP_CONCAT in SQLite / STRING_AGG in Postgres — one row per dept with comma-separated names.
7. Query 8: SUM of an empty set = NULL. If your dashboard shows blank instead of 0, this is why. Wrap in COALESCE(..., 0).
8. Query 9 works in SQLite (returns some arbitrary name per dept) but errors in Postgres — one of many reasons to develop against Postgres, not SQLite, for prod-like behaviour.

Try this modification: add an orders(user_id, amount, order_date) table with a year's worth of dates. Write a query returning revenue per (month, dept-of-buyer). You'll need strftime('%Y-%m', order_date) (SQLite) or DATE_TRUNC('month', ...) (Postgres) as a GROUP BY key.

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

Every dashboard you've ever seen is an aggregation query. Revenue by month = SUM GROUP BY DATE_TRUNC('month', …). Active users = COUNT(DISTINCT user_id). p95 latency = PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency). Learn GROUP BY deeply and you can build 80% of any BI tool from raw SQL.

War story — COUNT(DISTINCT) at petabyte scale. A social network computed daily active users as COUNT(DISTINCT user_id) FROM events WHERE event_date = today. On a 5 TB events table this took 4 hours and blew Redshift's memory. Solution: precompute HyperLogLog sketches per hour (APPROX_COUNT_DISTINCT in Redshift/BigQuery/Snowflake). Answer within 2% accuracy, in 30 seconds. Modern lesson: exact COUNT(DISTINCT) on billion-row tables is a code smell — reach for HLL.

War story — the "average of averages" bug. Marketing computed per-region average order value, then averaged those regional averages to get global AOV. The regions had wildly different order counts — average-of-averages doesn't equal true average. Off by 15%. Rule: you cannot aggregate an aggregate that way. Always aggregate from the row-level facts.

War story — the double-counting join. (Same story as S037.) After joining campaigns → emails → clicks, SUM(campaign_cost) was multiplied by (emails × clicks) per campaign. Fix: SUM(campaign_cost) was in the wrong CTE layer. Aggregate before joining fact-to-fact tables. This will re-appear in every SQL bug-fix conversation of your career.

Gotchas top teams handle:

• NULL semantics. SUM and AVG skip nulls silently. COUNT(*) counts them. Explicit WHERE col IS NOT NULL and COALESCE in SELECT are your safety net.
• Integer division in AVG. In some old MySQL / SQL Server modes, AVG(int_column) returns an integer. Cast to NUMERIC first: AVG(salary::numeric).
• GROUP BY 1, 2 (positional). Handy in ad-hoc SQL, but if you reorder SELECT columns you silently change the grouping. Ban in production code; use column names.
• HAVING without GROUP BY. Legal — treats the whole table as one group. Rarely what you meant.
• Cardinality of the grouping columns matters for performance. GROUP BY user_id on 100M rows produces 100M groups — that's a massive hash table. GROUP BY country produces ~200. Design your dashboards to aggregate on low-cardinality keys first, then drill down.
• BigQuery / Snowflake billing. GROUP BY scans full columns. On columnar storage this is cheap. On row-store Postgres, plan indexes carefully.

The senior-engineer tell: they say "aggregate first, join later" and reach for window functions (next session!) whenever they see themselves writing a correlated subquery.

(e) Quiz + exercise · 10 min

1. Explain the difference between WHERE and HAVING in one sentence, and give an example that only HAVING can express.
2. Why does COUNT(*) count more rows than COUNT(salary) on the employees table with Grace?
3. What does SUM(x) FROM tbl WHERE nothing_matches return, and how do you make it return 0?
4. Write a query returning depts where at least 2 employees earn more than 90k.
5. Explain why SELECT dept, name, AVG(salary) FROM employees GROUP BY dept errors in Postgres but silently succeeds in old MySQL. Which behaviour do you prefer and why?
6. Stretch (connects to S037 Joins): the "top earner per dept" query in this session used a correlated subquery. Rewrite it as a JOIN between employees and a GROUP BY dept, MAX(salary) subquery. Which one do you think is faster on a 10M-row table?

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is Aggregations? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S039): Window Functions — the Game-Changer
• Previous (S037): Joins — INNER, LEFT, RIGHT, FULL, Anti-Join
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.