Core CS · DBMS

Seven rows in, two rows out

Q: How do the aggregate functions treat NULL?

COUNT(*) is the only one that sees a NULL at all. SUM , AVG , MIN , MAX and COUNT(column) discard NULLs before they begin and then work on what is left, exactly as if those rows had never been fetched. That is why an average divides by the number of values rather than the number of rows, and it is why a missing value is never quietly treated as a zero.

Q: Why can you not write COUNT(*) >= 2 in WHERE?

Because WHERE runs before GROUP BY . At that moment the query still holds individual rows, no bucket has been built and no count exists, so there is nothing to compare with 2. The database rejects it as an error rather than guessing. HAVING runs after grouping and after the aggregates have been computed, which is exactly why the same test is legal there.

Aggregates are the only way a query can say something about more than one row at a time. You will follow one query over one seven-row table, clause by clause, and watch COUNT(*) and COUNT(marks) return different numbers for the same bucket.

Watch three buckets become two rows →

GROUP BY throws your rows away and hands back buckets. From that point on the query can name a grouping column or an aggregate and nothing else, because a bucket of three rows has no single sid to print.

01 The idea

After GROUP BY there are no rows left

Every query you have written so far answers a question about one row at a time. WHERE marks > 60 looks at a row, decides, and moves on. No amount of WHERE will tell you how many students there are, because counting is a statement about a set and a row does not know what set it is in.

An aggregate function is what closes that gap. It takes many values and returns one. Five of them carry almost every question you will be asked, and you already know what they mean in English: COUNT, SUM, AVG, MIN, MAX. Standard SQL defines more, the statistical ones such as STDDEV_POP among them, but those five are the exam set. On their own they collapse the whole result to a single row. GROUP BY is the instruction that says collapse it in pieces instead: sort the rows into buckets by the value of some column, and give me one row per bucket.

That word instead is the part people skim. Grouping is not a filter and it is not a sort. It replaces the rows with buckets, and everything downstream of it sees buckets. This one fact generates every rule in the lesson: why SELECT cannot name an ungrouped column, why a count cannot be tested in WHERE, why HAVING exists at all, and why a query with GROUP BY can return zero rows where the same query without it returns one.

WHERE filters rows, before any group exists. GROUP BY turns the surviving rows into buckets. HAVING filters the buckets. Work out which of those three a clause is doing and you can derive the rest.

Aggregate functionA function that takes many values and returns one: COUNT, SUM, AVG, MIN, MAX. Every one of them discards NULLs before it starts, with a single exception: COUNT(*), which counts rows and reads no column at all. Note that COUNT(marks) discards them like the rest.

GroupThe set of rows that share the same value in every GROUP BY column. One group becomes exactly one row of the answer, whether it holds one row or a thousand.

HAVINGThe filter that runs after grouping, which makes it the only place a test on an aggregate is allowed. WHERE runs before any group exists, so it has nothing to test.

02 Worked example

One query, five stops

One table for the whole lesson. mark holds one row per student per course, seven rows in all, and one of those rows has no marks value because that student was absent. That single NULL is not decoration. It is what makes two of the counts in the query disagree.

-- mark · seven rows, and one of them has no mark at all sid   course   term   marks S1    CSE       1      72 S2    CSE       1    NULL    -- absent; no mark exists, and NULL is not 0 S3    CSE       1      55 S4    ECE       1      64 S5    ECE       1      88 S6    MEC       1      41 S7    CSE       2      90    -- the only term 2 row in the table

And one query. Read it top to bottom the way it is written, then forget that order, because the database does not run it that way.

SELECT   course,         COUNT(*)     AS students,         COUNT(marks) AS graded,         AVG(marks)   AS avg_marksFROM     markWHERE    term = 1GROUP BY courseHAVING   COUNT(*) >= 2ORDER BY AVG(marks) DESC;

The clauses run in the order below, and each one hands a different shape to the next. Follow the row count across the five stops: 7 → 6 → 3 buckets → 2 buckets → 2 rows.

1 · FROM markseven rows are loaded, including S2 with no marks value

→

2 · WHERE term = 1tests one row at a time; S7 is term 2 and leaves here, before anything counts it

→

3 · GROUP BY coursethe six survivors become three buckets: CSE 3 rows, ECE 2, MEC 1. The rows are gone

→

4 · HAVING COUNT(*) >= 2tests whole buckets; MEC holds one row, so the MEC bucket is dropped as a unit

→

5 · SELECT, ORDER BYone row per surviving bucket, sorted by the average: ECE 76, then CSE 63.5

Stop 3 is the hinge and it is highlighted for that reason. Before it the query holds six rows, each with its own sid. After it the query holds three buckets, and a bucket has no sid: the CSE bucket contains S1, S2 and S3, and there is no rule that says which of those three to print. That is the whole reason for the restriction people memorise as “every non-aggregated column in SELECT must be in GROUP BY”.

Stop 2 and stop 4 both remove things, and it is worth being precise about the difference. WHERE removed S7 as a row, before it reached a bucket, so S7 could never have influenced any count. HAVING removed MEC as a bucket, after S6 had been counted into it. Same query, two different exits, and knowing which one a value took is what makes the numbers in the answer mean something.

03 Mechanics

Five functions, two filters, one rule

Start with the five functions and exactly what each does with a missing value, because that is where most wrong answers come from. The fourth column applies each one to the CSE bucket from section 02, which holds three rows and two marks: 72, NULL, 55. Work the arithmetic yourself before you read the number.

Function	Exact meaning	Rows where the column is NULL	On the CSE bucket: 72, NULL, 55	Over no rows at all
*COUNT()**	How many rows the group holds. It reads no column, so a row of nothing but NULLs still counts.	counted	`3`	`0`
COUNT(marks)	How many rows have a value in marks. It counts values, not rows.	`skipped`	`2`	`0`
COUNT(DISTINCT marks)	How many different non-NULL values are in marks.	`skipped`	`2`	`0`
SUM(marks)	The total of the non-NULL values. Nothing to add is not zero.	`skipped`	`72 + 55 = 127`	`NULL`
AVG(marks)	SUM(marks) divided by COUNT(marks), never by COUNT(*).	`skipped`	`127 / 2 = 63.5`	`NULL`
MIN(marks)	The smallest non-NULL value in the group.	`skipped`	`55`	`NULL`
MAX(marks)	The largest non-NULL value in the group.	`skipped`	`72`	`NULL`

Read the third column downwards and the whole NULL story is one line long: COUNT(*) is the only aggregate that sees a NULL at all. The other six entries discard NULLs first and then work on what is left, exactly as if those rows had never been fetched. So AVG(marks) on the CSE bucket is 127 divided by 2, which is 63.5. It is not 127 divided by 3, which is 42.33, and it is not 63: AVG is not integer division. Writing the same thing by hand as SUM(marks) / COUNT(marks) is a different story, because two integers divided in PostgreSQL, SQL Server or SQLite give an integer, so that expression prints 63. MySQL’s / always produces a decimal, so there it prints 63.5. One more vendor note worth carrying: SQL Server’s AVG returns the type of its argument, so AVG over an INT column truncates to 63 there too, and you cast the column to a decimal to avoid it.

Now the two filters. They read like synonyms in English and they are not interchangeable in any query that groups.

	WHERE	HAVING
When it runs	after FROM, before GROUP BY	after GROUP BY, once every aggregate has been computed
What it filters	one row at a time	one whole group at a time
May test an aggregate	`no, none exists yet`	`yes, that is what it is for`
May test a plain column	`yes, any column of the row`	`only a column named in GROUP BY`
May use a SELECT alias	`no, SELECT has not run`	`no in standard SQL; MySQL and SQLite allow it`
In our query	term = 1 removes the row S7	COUNT(*) >= 2 removes the MEC bucket
With no GROUP BY	filters rows as usual	tests the one implicit group; the query returns one row or none

The May test an aggregate row is the one interviewers probe. WHERE COUNT(*) >= 2 is not a slow query or a bad style choice. It is rejected before the query ever runs, and the When it runs row says why: at that moment there are no groups, so there is no count to compare against 2. The engine rejects it rather than guess. The mirror image also holds. Putting term = 1 in HAVING only compiles at all when term is a grouping column, and even then every row was sorted into a bucket and counted before being thrown away.

Four short variants, all against the same seven rows, cover the rest of what gets asked. Derive each result before reading it.

-- 1 · no GROUP BY: the whole result is one group, so you get exactly one rowSELECT COUNT(*), COUNT(marks), COUNT(DISTINCT course) FROM mark;-->  7   6   3        7 rows, 6 marks (S2 is NULL), 3 courses -- 2 · two grouping columns: one bucket per pair that actually occursSELECT course, term, COUNT(*) FROM mark GROUP BY course, term;-->  CSE 1 3 | CSE 2 1 | ECE 1 2 | MEC 1 1     four buckets, and no (ECE, 2) -- 3 · empty input, no GROUP BY: still exactly one rowSELECT COUNT(*), SUM(marks) FROM mark WHERE term = 9;-->  0   NULL             COUNT gives 0; SUM has nothing to add, so NULL -- 4 · empty input, with GROUP BY: no rows at allSELECT course, COUNT(*) FROM mark WHERE term = 9 GROUP BY course;-->  (no rows)            groups are built from rows; no rows, no groups

Variant 2 is the multi-column rule in one line: a bucket exists for every combination that appears in the data and for no combination that does not. Nobody has an ECE row in term 2, so there is no empty ECE-term-2 group sitting there with a count of zero. Variants 3 and 4 are the same WHERE clause returning different shapes, and the difference is entirely the GROUP BY. If a report needs a zero printed for a category with no rows, no aggregate will produce it, because there is no group to produce it from.

Two last details on ORDER BY, which runs after SELECT and therefore last. Because it runs on the grouped rows it can sort by an aggregate, including one that is nowhere in the select list, so ORDER BY MAX(marks) DESC is legal on our query even though MAX is not selected. And because SELECT has already run, the aliases exist: ORDER BY avg_marks DESC works where WHERE avg_marks > 60 could not. One vendor difference to have ready: on an ascending sort Oracle and PostgreSQL put NULLs last and let you write NULLS FIRST or NULLS LAST to change it, while MySQL and SQL Server sort NULLs first and have no such clause, so you spell the order out with an expression.

WHERE removes rows before anything counts them. HAVING removes buckets after everything has been counted. If a test names one row’s own column value it belongs in WHERE; if it names an aggregate, only HAVING can hold it.

05 Cheat sheet

The card to read on the morning

Every number in the right-hand column comes from the seven rows in section 02, so you can check the whole card by hand in about three minutes. Do that once and you will never again guess at what a NULL does to an average.

Write this	It means exactly	On the seven rows
*COUNT()**	rows in the group, NULLs and all	`7 over the whole table, 3 in the CSE bucket, 2 in ECE`
COUNT(marks)	rows where marks is not NULL	`6 over the whole table, 2 in the CSE bucket, 2 in ECE`
COUNT(DISTINCT course)	different non-NULL values, duplicates collapsed	`3, over any subset that still contains CSE, ECE and MEC`
SUM(marks)	total of the non-NULL values	`410 over the whole table, 127 in the CSE bucket, 152 in ECE`
AVG(marks)	SUM over COUNT of the same column, NULLs excluded from both	`the CSE bucket is 127 / 2 = 63.5, and ECE is 152 / 2 = 76`
MIN(marks), MAX(marks)	smallest and largest non-NULL value	`55 and 72 in the CSE bucket`
WHERE	filters rows, before any group exists	`term = 1 removes S7 before it is counted`
HAVING	filters groups, after the aggregates exist	`COUNT(*) >= 2 removes the one-row MEC bucket`
GROUP BY a, b	one bucket per distinct pair present in the data	`4 buckets over all 7 rows, and no (ECE, 2)`
ORDER BY AVG(marks) DESC	sorts the grouped rows; runs last, so aliases and unselected aggregates both work	`ECE 76 before CSE 63.5`
Aggregate, no GROUP BY, no rows	one row still comes back	`COUNT(*) gives 0 but SUM, AVG, MIN and MAX all give NULL`
Aggregate, GROUP BY, no rows	no groups can be built, so nothing comes back	`zero rows, not a row of zeroes`

Only COUNT(*) sees a NULLSUM, AVG, MIN, MAX and COUNT(column) discard NULLs before they start, so they behave as if those rows were never fetched. The consequence that gets tested: AVG divides by the number of values, not the number of rows, and a missing mark is never a zero.

Rows go in WHERE, groups go in HAVINGA test that looks only at one row’s own columns belongs in WHERE, where the row leaves before anything counts it. A test on an aggregate has nowhere to live but HAVING, because the aggregate does not exist until after grouping. Both clauses in one query is completely normal, and so is neither.

Empty input has two different answersWith no GROUP BY the query returns exactly one row even when nothing matched: 0 for COUNT(*) and NULL for everything else. Add GROUP BY and the same query returns zero rows, because a group has to be built out of at least one row.

06 Where & why

The rule every engine agrees on, and the four places they do not

Aggregates and HAVING behave the same everywhere, which is why they are safe interview ground. The grouping rule is where engines diverge, and a candidate who has only ever used one of them tends to state that engine’s habit as the standard. Learn the standard, then know which one you are typing into.

PostgreSQL

Strict, with one principled exception

Bare columns in SELECT are rejected, except where they are functionally determined by the grouping: group by a table’s primary key and you may select that table’s other columns, because the key fixes them. AVG over an integer column returns numeric, so 63.5 is exact. It also has FILTER (WHERE ...), which lets one query carry several differently-filtered counts without a single CASE.

MySQL

Strict now, notorious for what it used to do

ONLY_FULL_GROUP_BY has been part of the default sql_mode since 5.7. Before that a bare column returned a value from some arbitrary row of the bucket, with no warning, which is the origin of a great many reports that a query “worked before the upgrade”. MySQL also accepts a SELECT alias inside HAVING, which standard SQL does not and PostgreSQL rejects, so a query written there can fail elsewhere unchanged.

SQLite

Permissive, and specified about it

It allows bare columns in an aggregate query and takes them from an arbitrary row of the group. There is one documented exception that people rely on deliberately: when the query contains exactly one aggregate and that aggregate is MIN or MAX, the bare columns come from the row that produced it, which is a neat way to fetch the top-scoring student per course. It is also completely non-portable.

Oracle · SQL Server

Strict, no exception, plus the subtotal extensions

Neither implements the functional-dependency exception, so grouping by sid does not license selecting name. Both add ROLLUP, CUBE and GROUPING SETS for subtotal rows in one pass. Two traps worth naming: SQL Server’s AVG over an INT column returns an INT and truncates, and it has no NULLS LAST clause, so ordering by an aggregate that can be NULL needs an explicit expression.

The portable habit is the strict one. Name every column you select in GROUP BY or wrap it in an aggregate, put row tests in WHERE and group tests in HAVING, and never rely on a bare column: a query written that way returns the same rows on all five engines, and it is also the version an interviewer is marking against.

07 Interview questions

What they actually ask

Grouping is the part of SQL an interviewer can test on a whiteboard in ninety seconds, so it comes up constantly. Almost every one of these questions is really the same question underneath: at the moment this clause runs, does the query hold rows or buckets?

What does COUNT(*) do that COUNT(column) does not?

COUNT(*) counts rows and reads no column, so a row made entirely of NULLs still counts. COUNT(column) counts only the rows where that column holds a value. On a bucket of three rows where one student was absent, COUNT(*) is 3 and COUNT(marks) is 2. COUNT(DISTINCT column) goes one step further and counts different non-NULL values, so duplicates collapse as well.

How do the aggregate functions treat NULL?

COUNT(*) is the only one that sees a NULL at all. SUM, AVG, MIN, MAX and COUNT(column) discard NULLs before they begin and then work on what is left, exactly as if those rows had never been fetched. That is why an average divides by the number of values rather than the number of rows, and it is why a missing value is never quietly treated as a zero.

AVG over the values 72, NULL and 55. What comes back, and why?

63.5. AVG is SUM over COUNT of the same column: 72 plus 55 is 127, COUNT(marks) is 2, and 127 divided by 2 is 63.5. It is not 42.33, which is what you get by dividing by all three rows as though the NULL were a zero. The follow-up is worth having ready: if that student had sat the exam and scored 0 rather than being absent, the average really would be 42.33, because a 0 is a value and a NULL is not.

Why can you not write COUNT(*) >= 2 in WHERE?

Because WHERE runs before GROUP BY. At that moment the query still holds individual rows, no bucket has been built and no count exists, so there is nothing to compare with 2. The database rejects it as an error rather than guessing. HAVING runs after grouping and after the aggregates have been computed, which is exactly why the same test is legal there.

WHERE and HAVING both filter. When do you use which?

WHERE filters rows before grouping; HAVING filters whole groups after it. A test that looks only at one row’s own column values belongs in WHERE, because the row then leaves before anything counts it. A test on an aggregate has nowhere else to live but HAVING. Both in the same query is completely normal, and so is a grouped query with neither.

Why must every non-aggregated column in SELECT appear in GROUP BY?

Because after grouping the query holds one row per bucket, not rows. A grouping column has a single value across its whole bucket by construction, and an aggregate collapses the bucket to one value, so both can be printed. A bare column like sid has three different values in a three-row bucket and no rule picks one, so standard SQL rejects it. The one principled exception is in the standard itself: functional dependency, where grouping by a table’s primary key determines its other columns, and PostgreSQL is the mainstream engine that implements it.

What happens when you GROUP BY two columns?

You get one bucket per distinct combination of the two values that actually occurs in the data. Grouping marks by course and term turns three CSE rows in term 1 and one CSE row in term 2 into two separate buckets. Combinations that are absent produce no bucket at all, so there is no empty group sitting there with a count of zero waiting to be reported.

What if the GROUP BY column itself contains NULL?

All the NULLs collapse into a single group, even though NULL = NULL is never true. Grouping deliberately compares values as “not distinct from” rather than with equality, so every row with a missing course lands in the same bucket and that bucket prints NULL in its label column. It is one of very few places in SQL where two NULLs are treated as the same thing, and it is a favourite follow-up question.

What does a query with an aggregate and no GROUP BY return?

Exactly one row. Everything that survived FROM and WHERE is treated as a single implicit group. That still holds when nothing survived, which is why SELECT COUNT(*) FROM mark WHERE term = 9 returns one row containing 0, and the same query with SUM(marks) returns one row containing NULL.

And the same query with GROUP BY, over an empty input?

Zero rows. Groups are built out of rows, so no rows means no groups and there is nothing to hand back. That is the trap: the same WHERE clause gives you one row holding a 0 and some NULLs without GROUP BY, and an entirely empty result with it. If a report needs a zero printed for a category that has no rows, no aggregate will produce it, because there is no group to produce it from.

Can HAVING appear without GROUP BY?

Hard

Compute two numbers to two decimal places over the seven rows: (a) the result of SELECT AVG(marks) FROM mark; and (b) the plain arithmetic mean of the three per-course averages returned by SELECT course, AVG(marks) FROM mark GROUP BY course; Explain in one sentence why they differ, then write a single query that returns the number in (a) using SUM and COUNT only, with no AVG anywhere in it.

Follow-up

The NULL looks like the culprit and is not: replace S2’s NULL with a 0, redo both numbers, and they still disagree. Name the one property of the three per-course buckets that is actually responsible, say what would have to be true of those buckets for (a) and (b) to come out equal, then predict whether your SUM-over-COUNT query prints the two-decimal number or a whole one and say what decides that.

Show the hint

Write (a) as a weighted average of the three per-course averages, then look hard at what the three weights turn out to be.