SQL Aggregation and Grouping

1. What Is It?

Aggregation collapses multiple rows into a single summary value — a count, sum, average, minimum, or maximum. GROUP BY divides the result set into groups first, then aggregates each group independently. HAVING filters those groups after aggregation, the same way WHERE filters rows before aggregation.

These are the primitives behind analytics dashboards, reporting endpoints, and any "how many / how much / what's the top N" query your application runs.

2. How It Works

Copy
-- Basic aggregate: count all paid orders
SELECT COUNT(*) AS paid_order_count
FROM orders
WHERE status = 'paid';

-- GROUP BY: aggregate per group
SELECT
    user_id,
    COUNT(*)             AS order_count,
    SUM(total_cents)     AS total_spent_cents,
    AVG(total_cents)     AS avg_order_cents,
    MAX(placed_at)       AS last_order_at
FROM orders
WHERE status = 'paid'
GROUP BY user_id;
-- One result row per unique user_id

The rule: In a SELECT that uses GROUP BY, every column in the SELECT list must either be in the GROUP BY clause or be wrapped in an aggregate function. The database raises an error otherwise because a non-grouped, non-aggregated column is ambiguous — if a user has 5 orders, which order's placed_at do you want?

HAVING filters after aggregation:

Copy
-- Users who spent more than $500 total
SELECT
    user_id,
    SUM(total_cents) AS total_spent_cents
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING SUM(total_cents) > 50000;  -- 50000 cents = $500

Execution order:

1. FROM / JOIN — identify source rows
2. WHERE — filter individual rows before grouping
3. GROUP BY — divide into groups
4. HAVING — filter groups
5. SELECT — compute output columns
6. ORDER BY — sort output
7. LIMIT / OFFSET — truncate output

-+ResetFullscreen

Common aggregate functions:

3. What SDEs Actually Need to Know

COUNT(*) vs. COUNT(col) vs. COUNT(DISTINCT col)

Copy
-- COUNT(*): count all rows, including those with NULLs
SELECT COUNT(*) FROM orders;  -- total rows in table

-- COUNT(col): count rows where col is NOT NULL
SELECT COUNT(coupon_code) FROM orders;  -- only orders that used a coupon

-- COUNT(DISTINCT col): count unique non-NULL values
SELECT COUNT(DISTINCT user_id) FROM orders;  -- unique customers who ordered

Mixing these up is a frequent bug source. If COUNT(DISTINCT col) returns a much smaller number than expected, check whether the column has unexpected NULLs.

NULL in aggregates: SUM, AVG, MIN, MAX skip NULL values silently. SUM of a column that's all NULL returns NULL, not 0. Use COALESCE(SUM(col), 0) if you need 0 for empty groups.

WHERE vs. HAVING:

• WHERE runs before grouping — use it to filter source rows (e.g., only paid orders)
• HAVING runs after grouping — use it to filter groups based on aggregate values (e.g., users with more than 5 orders)
• Using HAVING where WHERE would do is slower because it aggregates all rows first, then discards groups

GROUP BY column position (avoid it):

Copy
-- Valid but fragile: GROUP BY 1 means GROUP BY the first SELECT column
SELECT user_id, COUNT(*) FROM orders GROUP BY 1;

-- Better: name the column explicitly
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

Column positions shift when you add or reorder SELECT columns. Name your GROUP BY columns explicitly.

GROUP BY multiple columns: Groups are defined by the unique combination of all grouped columns.

Copy
-- Orders per user per status
SELECT user_id, status, COUNT(*)
FROM orders
GROUP BY user_id, status;
-- One row per (user_id, status) pair

4. Tradeoffs & Decisions

Aggregating in SQL vs. aggregating in application code

It's tempting to fetch all rows and aggregate in your application:

Copy
orders = db.query("SELECT * FROM orders WHERE user_id = ?", user_id)
total = sum(o.total_cents for o in orders)

At small scale this works. At scale, the database transfers potentially millions of rows to your application just to compute a single number. Move aggregation into SQL — the database computes it on-disk or in-memory, transfers one row back, and uses indexes to skip unneeded data.

Pre-aggregated tables (materialized aggregates)

For dashboards with expensive aggregations over large tables, some teams pre-aggregate into summary tables updated by scheduled jobs:

Copy
CREATE TABLE daily_revenue (
    date        DATE PRIMARY KEY,
    total_cents BIGINT NOT NULL
);
-- Populated by a nightly job

The tradeoff: reads become instant, but data is stale (as old as the last job run) and you need to maintain the aggregation logic separately. Use this when query time exceeds user tolerance and real-time data isn't required.

ROLLUP and CUBE for multi-level subtotals

GROUP BY ROLLUP(a, b) adds subtotal rows at each level of the grouping hierarchy. Useful for reports but rarely needed in application code.

5. Interview Cheat Sheet

Key sentences:

• "GROUP BY divides rows into groups; aggregate functions then reduce each group to a single value."
• "Every SELECT column must appear in GROUP BY or be wrapped in an aggregate — otherwise which row's value would you return?"
• "HAVING filters groups after aggregation; WHERE filters rows before. Using HAVING instead of WHERE for simple row filters is slower."
• "COUNT(col) skips NULLs; COUNT(*) does not. COUNT(DISTINCT col) gives you unique non-null values."

Common follow-ups:

Q: What does it mean when SUM returns NULL instead of 0? A: SUM over an empty set (no rows match the WHERE clause) returns NULL in SQL. Use COALESCE(SUM(col), 0) to convert NULL to 0, especially in LEFT JOIN queries where the aggregated table might have no matching rows.

Q: Can you use a column alias in HAVING? A: No — because HAVING is evaluated before SELECT computes aliases (see execution order). You must repeat the expression: HAVING SUM(total_cents) > 50000, not HAVING total_spent > 50000. PostgreSQL allows alias references in GROUP BY (a non-standard extension), but not in HAVING.

Q: How would you find duplicate rows in a table? A: GROUP BY the columns that define uniqueness, then HAVING COUNT(*) > 1:

Copy
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;