Aggregate functions — COUNT, SUM, AVG, MIN, MAX: Question bank

Picture the pipeline first

Before any trap makes sense, you need the logical query pipeline burned into your eyes. This is the fixed order in which the database thinks about a query — not the order you typed it. Almost every "why is this an error?" answer on this page points back to this one picture.

Figure — Aggregate functions — COUNT, SUM, AVG, MIN, MAX

Watch aggregates skip NULLs

The single most common trap is forgetting that NULL means "unknown" and gets silently dropped from SUM, AVG, MIN, MAX, and COUNT(column) — but a row still exists, so COUNT(*) still counts it. The figure below walks one column through both paths.

Notice the divergence this creates with real numbers:

Three flavours of COUNT

COUNT(*), COUNT(column), and COUNT(DISTINCT column) answer three genuinely different questions. Fix the picture in your head and you'll never mix them up.

How GROUP BY treats NULL

Here is the twist that catches everyone: aggregates skip NULL, but GROUP BY keeps NULL as its own single group key. So a NULL department doesn't vanish — all NULL-dept rows huddle into one "NULL" bucket. The figure contrasts the two opposite behaviours side by side.

Edge cases, at a glance

The abstract boundary rules (zero rows, one-row groups, all-NULL groups) are easiest to trust once you see all three side by side with their actual return values.

True or false — justify

Each line is a claim. Decide true or false, then justify in one breath before revealing.

COUNT(*) and COUNT(column) always return the same number.

False — they match only when the column has zero NULLs. COUNT(*) counts rows (empty desks included); COUNT(column) skips NULLs. Any NULL in that column makes COUNT(column) smaller.

AVG(x) always equals SUM(x) / COUNT(*).

False — AVG divides by COUNT(x) (non-NULL count), not COUNT(*). They agree only when no value is NULL; with a NULL present, COUNT(*) is too big and gives a wrong, too-small average.

SUM of a column that is entirely NULL returns 0.

False — it returns NULL, because there were no real numbers to add. Only COUNT returns 0 over an empty/all-NULL set; SUM and AVG return NULL to signal "nothing to work with".

Adding one NULL row to a table never changes AVG(salary).

True for the value of AVG — the NULL is skipped in both sum and count, so the average is untouched. But it does raise COUNT(*) by one, which is why SUM/COUNT(*) would drift.

MIN(salary) can return NULL even when some salaries are numbers.

False — MIN/MAX ignore NULLs, so as long as at least one non-NULL value exists, they return a real number. They return NULL only when every value is NULL.

WHERE salary > AVG(salary) is a valid line inside a simple query.

False — WHERE runs before aggregation in the pipeline, so no aggregate exists yet. You need a subquery or HAVING, depending on whether you're filtering rows or groups.

Using GROUP BY dept guarantees the result has one row per department.

True — GROUP BY collapses each distinct group key into exactly one output row, and the aggregates summarise the rows inside that group.

COUNT(DISTINCT col) includes NULL as one of its distinct values.

False — DISTINCT inside COUNT still obeys the NULL-skip rule, so NULL is never counted as a value. Only distinct non-NULL values are tallied.

AVG(COALESCE(salary, 0)) gives the same answer as AVG(salary).

False — COALESCE turns NULLs into real zeros, so they now get counted. This lowers the average because the denominator grew and zeros were added to the sum.

A multi-column GROUP BY dept, team makes one group per department.

False — it makes one group per unique combination of (dept, team). Two teams in the same department become two separate groups, so you can get more rows than distinct departments.

ROLLUP(dept, team) produces the same rows as a plain GROUP BY dept, team.

False — ROLLUP adds subtotal rows (per-dept totals and a grand total) on top of the detailed groups, marking the rolled-up columns with NULL to signal "all values".

Spot the error

Each query below has a conceptual bug. Say what breaks and why.

SELECT dept, salary FROM employees GROUP BY dept; — what's illegal?

salary is neither aggregated nor in GROUP BY. Each dept group holds many salary values, so the database can't pick one — standard SQL rejects this mixing of a row-level column with a collapsed group.

SELECT dept, AVG(salary) FROM employees WHERE AVG(salary) > 200 GROUP BY dept; — fix it.

The aggregate condition belongs in HAVING, not WHERE. WHERE runs before GROUP BY, so AVG(salary) doesn't exist yet. Move AVG(salary) > 200 into a HAVING clause after GROUP BY.

SELECT AVG(salary) AS a FROM employees WHERE a > 100; — why the error?

The alias a is defined in SELECT, which runs after WHERE in the pipeline. WHERE can't see names that haven't been created yet, so a is unknown at that point.

SELECT COUNT(salary) FROM employees; intending to count all employees — the trap?

It counts only rows with a non-NULL salary, silently undercounting anyone whose salary cell is empty. To count every employee, use COUNT(*).

SELECT SUM(salary)/COUNT(*) FROM employees; intending the average salary — the flaw?

This divides the sum of real salaries by the total row count, including NULL-salary rows. That's not the true average; the honest one is AVG(salary) (or SUM/COUNT(salary)), which divides by the non-NULL count.

SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING salary > 100; — what's wrong?

After grouping, individual salary values no longer exist — only group-level summaries do. HAVING must reference an aggregate like MAX(salary) > 100, not a bare row column.

SELECT dept, team, AVG(salary) FROM employees GROUP BY dept; — why does it fail?

team is selected but not grouped or aggregated. In a multi-column grouping situation, every non-aggregated SELECT column must appear in GROUP BY; add team to the GROUP BY list.

Why questions

Why does COUNT(*) count NULL rows but COUNT(col) doesn't?

COUNT(*) asks "how many rows exist?" — a row's existence is a fact even if a cell is blank. COUNT(col) asks "how many known values does this column have?", and NULL means unknown, so it's skipped. (See the "Three flavours of COUNT" figure — the empty cell survives one path and vanishes from the other.)

Why do SUM and AVG return NULL over an empty set but COUNT returns 0?

Counting nothing is a definite answer: zero things. But summing or averaging nothing has no honest numeric answer, so SQL returns NULL to say "undefined / no data". (The edge-case figure shows all three return values stacked together.)

Why can HAVING see aggregates while WHERE cannot?

In the logical pipeline, WHERE runs before GROUP BY and aggregation, so aggregates don't exist yet. HAVING runs after aggregation, so the summary values are already computed and available to filter on. (Trace it on the pipeline conveyor-belt figure: WHERE sits upstream of the aggregate box, HAVING downstream.)

Why is AVG never just "sum divided by the number of rows you see"?

Because NULLs occupy rows but contribute no value. AVG defines the denominator as the count of real values, keeping the mean honest — you can't average in an "unknown". (The 150-vs-200 divergence figure is exactly this in numbers.)

Why does DISTINCT go inside the aggregate as COUNT(DISTINCT x), not outside?

The deduplication must happen on the column's values before they're counted. DISTINCT inside COUNT strips duplicate values first, then tallies what remains; placing it elsewhere wouldn't scope it to that aggregate.

Why does GROUP BY come before HAVING but after WHERE in execution?

WHERE trims rows first (cheaper, fewer rows to group), then GROUP BY forms the buckets and aggregates run, and only then can HAVING judge each bucket by its summary. The order mirrors "filter rows → build groups → filter groups", which is exactly the conveyor belt in the pipeline figure.

Why does GROUP BY keep NULL as a group but SUM throws it away?

They answer different questions. GROUP BY asks "which rows share a key?" — and "having no key" is itself a shared trait, so NULLs cluster into one group. SUM asks "what do these values add to?" — and an unknown can't be added, so it's dropped. (The GROUP-BY-NULL figure shows both rules living on the same data.)

Edge cases

A table with zero rows — what does COUNT(*) return, and SUM(x)?

COUNT(*) returns 0 (there are zero rows), while SUM(x) returns NULL (no values to add). This mirrors the empty-set rule shown in the edge-case figure.

A GROUP BY on a table where every group has exactly one row — is aggregating pointless?

Not illegal, just trivial — each aggregate summarises a single value, so AVG equals that value, MIN=MAX, and COUNT(*)=1 per group. It's valid, just returns the rows nearly unchanged.

If a whole department's salaries are all NULL, what does its AVG(salary) show in a grouped query?

NULL — the group has no non-NULL values, so both sum and count-of-values collapse to "nothing". The department still appears as a row (it has rows), but its average is NULL.

Does GROUP BY create a group for the NULL value of the grouping column?

Yes — unlike aggregates, GROUP BY treats NULL as a single group key, so all NULL-dept rows land together in one "NULL" group. This is the opposite of the skip-NULL behaviour aggregates use on their arguments.

MIN and MAX on a text column — do they work, and how?

Yes — they return the alphabetically/collation-first and last strings, not numbers. MIN/MAX order by whatever the column's type comparison rule is, so they aren't limited to numeric columns.

SUM of a column mixing large positives and negatives that cancel to zero — is that the same as an all-NULL SUM?

No — a genuine sum of 0 means "real values that added to zero", returning 0. An all-NULL SUM returns NULL, meaning "no values at all". The two look similar but signal completely different situations.

If you wrap COALESCE(SUM(x), 0), what boundary case are you defending against?

The all-NULL / empty-set case where SUM returns NULL. COALESCE swaps that NULL for a clean 0 so downstream math or displays don't break on an unexpected NULL.

In GROUP BY ROLLUP(dept, team), what do the extra NULLs in the result mean?

They are not missing data — they mark subtotal rows where that column has been "rolled up" over all its values. A NULL in the team column of a rollup row means "this is the total across all teams in that department".

How does CUBE(dept, team) differ from ROLLUP(dept, team) in what totals it adds?

ROLLUP gives an ordered hierarchy of subtotals (per dept, then grand total). CUBE adds subtotals for every combination of the grouping columns — per dept, per team, and the grand total — so you also get per-team totals across all departments.

Recall One-line summary of the whole trap-space

Almost every aggregate bug traces back to two facts: (1) aggregates skip NULLs but COUNT(*) counts rows, and (2) the pipeline order FROM → WHERE → GROUP BY → aggregate → HAVING → SELECT → ORDER BY decides who can see what.

Connections

Parent topic — Aggregate functions
NULL handling in SQL — the skip-NULL rule behind most of these traps.
GROUP BY and HAVING — grouping and group-level filtering.
SQL query execution order — why WHERE ≠ HAVING.
COALESCE and NULLIF — defending against NULL from SUM/AVG.
DISTINCT keyword — deduping inside COUNT.