DSAMotivator

Aggregation

SQL · 6 interview questions

Aggregate functions collapse many rows into one value. The rule that explains nearly all of their surprising behaviour is that every aggregate except COUNT(*) ignores NULLs entirely.

So COUNT(*) counts rows, while COUNT(column) counts rows where that column isn't NULL — and the gap between the two numbers is precisely the number of NULLs. That's occasionally a useful trick and frequently an accidental bug.

AVG follows the same rule, and this is where it gets genuinely dangerous. AVG divides by the count of non-NULL values, not by the number of rows. If missing data should count as zero, AVG is silently giving you the wrong answer and you need SUM(col) / COUNT(*) or a COALESCE.

The other half of aggregation is what happens when there are no rows at all. COUNT returns 0, but SUM, AVG, MIN and MAX all return NULL, not zero. Any query whose result feeds into arithmetic needs to decide what an empty group means — usually via COALESCE, and usually only after someone has been surprised by it once.

Aggregation interview questions

What's the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. The difference between the first two is exactly the number of NULLs in the column.
Why they ask: The most reliable way to find out whether someone understands NULL semantics.
Does AVG treat NULLs as zero?
No — it ignores them completely, dividing the sum by the count of non-NULL values. If missing values should count as zero, you need SUM(col) / COUNT(*) or AVG(COALESCE(col, 0)), which are the same thing written two ways.
Why they ask: A silently wrong number is worse than an error, and this produces one.
What does SUM return over zero rows?
NULL, not 0. So do AVG, MIN and MAX. COUNT is the exception and returns 0. Wrap it in COALESCE when the result feeds into arithmetic, or the NULL will propagate through everything downstream.
Why they ask: Catches people who assume aggregates always return a number.
How do you count distinct combinations of two columns?
MySQL allows COUNT(DISTINCT a, b) directly. Standard SQL doesn't, so the portable form is to count over a derived table: SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t) x. Beware that in MySQL, DISTINCT ignores rows where any listed column is NULL.
Why they ask: A dialect difference with a portable answer — good candidates give both.
Why does an integer percentage calculation come out as 0?
Integer division truncates. COUNT(x) / COUNT(*) on integers gives 0 before it ever reaches the multiply-by-100. Force a floating-point division first — multiply by 100.0, or cast — then round.
Why they ask: Half of all 'percentage of users who…' problems are really this question.
Can you SELECT a column that isn't in the GROUP BY and isn't aggregated?
Not in standard SQL, and not in MySQL with ONLY_FULL_GROUP_BY enabled (the modern default). Older MySQL allowed it and returned an arbitrary row's value, which is why so much legacy SQL does this and appears to work.
Why they ask: Explains why a query that ran on the old server errors on the new one.

Practise it

Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.

You'll forget most of this by next week

That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.

Start your streak

Full refund within 7 days. No questions.