Aggregate functions — COUNT, SUM, AVG, MIN, MAX: Worked examples

This page is the drill hall. The parent note taught you the five functions and the golden NULL rule. Here we hit every corner case — the empty table, the all-NULL column, the single row, the mixed group, the exam trap — so no result ever surprises you.

The scenario matrix

Every aggregate query lands in one of these case classes. The right column names the example that covers it.

#	Case class	What makes it tricky	Covered by
C1	All values present, no NULL	The "clean" baseline	Ex 1
C2	Some NULLs mixed in	AVG divides by non-NULL count; MIN/MAX skip NULL	Ex 2
C3	A whole column is NULL	SUM/AVG → NULL, COUNT → 0	Ex 3
C4	Empty table (zero rows)	Degenerate: nothing to fold	Ex 4
C5	Single row	Limiting case: MIN = MAX = AVG	Ex 5
C6	Grouped with an all-NULL group	GROUP BY meets the NULL rule	Ex 6
C7	DISTINCT inside COUNT/SUM	Duplicates removed first	Ex 7
C8	Negative + zero values	Signs in SUM/MIN/MAX	Ex 8
C9	Real-world word problem	Translate business question → SQL	Ex 9
C10	Exam twist: WHERE vs HAVING vs COALESCE	The classic trap combo	Ex 10

Two pictures will anchor the whole page. The first shows what an aggregate is — a vertical column of four salary cells (with one NULL cell marked in red) collapsing, along a single arrow, into one summary box holding 600. The red NULL cell reminds you that it is skipped before the fold:

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

The second shows the two degenerate boundaries side by side, so you can see they give the same answers. Left panel — an all-NULL column of three red NULL cells: COUNT(*) = 3, COUNT(col) = 0, but SUM = NULL and AVG = NULL. Right panel — an empty table (a single dashed box labelled "no rows" in red): COUNT(*) = 0, and SUM/MIN/MAX = NULL. The caption underneath states the rule both panels obey: nothing to add → NULL; nothing to count → 0:

Keep both in view as we work.

Worked examples

We reuse the parent's table where useful, and introduce small tables per case. Read the Forecast and guess the answer yourself before scrolling.

Base table employees (same as parent):

id	dept	salary
1	A	100
2	A	200
3	B	300
4	B	NULL

Worked example Ex 1 — Clean baseline ·

SELECT COUNT(*), COUNT(dept), SUM(salary)
FROM employees
WHERE salary IS NOT NULL;

Forecast: how many rows survive the filter, and what totals do they give?

Apply the WHERE first. Rows 1, 2, 3 survive (row 4 has NULL salary, so salary IS NOT NULL is false for it). Why this step? By the execution pipeline $FROM \to WHERE \to \dots$ , filtering happens before any aggregate runs. After this line the aggregate only ever sees a clean, NULL-free column.
COUNT(*) = 3. Three rows remain. Why this step? COUNT(*) counts rows, and exactly three passed the filter.
COUNT(dept) = 3, SUM(salary) = 600. No NULLs left, so nothing is skipped: $100 + 200 + 300 = 600$ . Why this step? Once NULLs are filtered away, every function agrees on the row count — the trap of Ex 2 vanishes.

Result: 3, 3, 600. Verify: $100 + 200 + 300 = 600$ ✓. Since the filter removed the only NULL, COUNT(*) and COUNT(dept) must be equal — they are (both 3). ✓

Worked example Ex 2 — NULLs mixed in (incl. MIN/MAX) ·

SELECT COUNT(*), COUNT(salary), SUM(salary), AVG(salary),
       MIN(salary), MAX(salary)
FROM employees;

Forecast: the parent showed the first four as 4, 3, 600, 200. Guess why AVG is 200 and not 150, and guess what MIN and MAX do with the NULL row, before reading.

COUNT(*) = 4. Every row is a fact, NULL cell or not. Why this step? The star counts desks, not values (see the mnemonic "stars count empties").
COUNT(salary) = 3. Row 4's salary is NULL, skipped. Why this step? COUNT(col) counts non-NULL values only.
SUM(salary) = 600. $100 + 200 + 300$ ; NULL contributes nothing. Why this step? You cannot honestly add an unknown to a total, so SQL drops it.
AVG(salary) = SUM / COUNT(salary) = 600 / 3 = 200. Why this step? AVG divides by the non-NULL count (3), not the row count (4). If it divided by 4 you'd get 150 — the trap.
MIN(salary) = 100, MAX(salary) = 300. Why this step? MIN and MAX also skip NULL — they only compare the known values {100, 200, 300}. So the NULL row does not become a "smallest" value: NULL is not a number below zero, it is simply absent. The smallest known is 100, the largest known is 300.

Result: 4, 3, 600, 200, 100, 300. Verify: $600/3 = 200 \neq = 600/4 = 150$ ; and MIN/MAX ignore NULL, so they equal the extremes of {100,200,300}. ✓ (See NULL handling in SQL.)

Worked example Ex 3 — A whole column is NULL ·

C3 Table bonuses:

id	bonus
1	NULL
2	NULL
3	NULL

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus)
FROM bonuses;

Forecast: three of these four answers are the same weird value. Which value, and which three?

COUNT(*) = 3. Three rows exist regardless of content. Why this step? Rows are facts; emptiness of a cell doesn't erase the row.
COUNT(bonus) = 0. Not one non-NULL value exists. Why this step? COUNT(col) returns a genuine 0 here — a real number meaning "no known values".
SUM(bonus) = NULL. There is nothing to add. Why this step? This is the subtle boundary: SUM over an empty set of numbers is NULL, not 0. This is exactly the left panel of the boundary figure above — three red NULL cells, yet SUM collapses to NULL.
AVG(bonus) = NULL. It would be $SUM / COUNT(bonus) = NULL /0$ — undefined, so SQL returns NULL. Why this step? No values means no meaningful average.

Result: 3, 0, NULL, NULL. Verify: COUNT returns a number (3 and 0); SUM/AVG return NULL. To force 0 instead, use COALESCE(SUM(bonus),0) (see COALESCE and NULLIF). ✓

Worked example Ex 4 — Empty table, zero rows ·

C4 Table logins has no rows at all.

SELECT COUNT(*), SUM(duration), MIN(duration), MAX(duration)
FROM logins;

Forecast: what does the database hand back when there is literally nothing to fold?

COUNT(*) = 0. Zero rows means zero. Why this step? Counting the empty set gives 0 — a defined, honest answer.
SUM(duration) = NULL. No numbers to add. Why this step? Same boundary rule as Ex 3: SUM of an empty set is NULL. This is the right panel of the boundary figure above — the dashed "no rows" box — showing that an empty table and an all-NULL column behave identically for SUM/AVG/MIN/MAX.
MIN = NULL, MAX = NULL. There is no smallest or largest of nothing. Why this step? Extremes require at least one value; with none, they too collapse to NULL.

Result: 0, NULL, NULL, NULL. Verify: Only COUNT(*) is a number (0); all others NULL. This is the aggregate identity: an aggregate query over zero rows always returns exactly one row — the summary row — never zero rows. ✓

Worked example Ex 5 — Single row, the limiting case ·

C5 Table solo:

id	value
1	42

SELECT COUNT(*), SUM(value), AVG(value), MIN(value), MAX(value)
FROM solo;

Forecast: four of these five answers are equal. Predict them.

COUNT(*) = 1. One row. Why this step? COUNT(*) counts rows, and this table has exactly one row — the baseline every other function below is measured against.
SUM = 42, AVG = 42. $42/1 = 42$ . Why this step? With a single value, its sum, its average, and the value itself all coincide.
MIN = 42, MAX = 42. The only value is simultaneously smallest and largest. Why this step? This is the limiting boundary: as a group shrinks to one element, the "spread" (MAX − MIN) goes to 0.

Result: 1, 42, 42, 42, 42. Verify: $MAX - MIN = 42 - 42 = 0$ and $AVG = SUM = MIN = MAX$ . ✓

Worked example Ex 6 — GROUP BY meets an all-NULL group ·

C6 Table teams:

team	pts
X	10
X	20
Y	NULL
Y	NULL

SELECT team, COUNT(*), COUNT(pts), SUM(pts), AVG(pts)
FROM teams
GROUP BY team;

Forecast: team Y has two rows but no known points. What are Y's four numbers?

GROUP BY splits into two mini-tables: X = {10, 20}, Y = {NULL, NULL}. Why this step? Each aggregate then runs inside its group, independently. (See GROUP BY and HAVING.)
Team X → COUNT(*)=2, COUNT(pts)=2, SUM=30, AVG=15. Why this step? Clean group: $10 + 20 = 30$ , $30/2 = 15$ .
Team Y → COUNT(*)=2, COUNT(pts)=0, SUM=NULL, AVG=NULL. Why this step? Y's group is an all-NULL column — inside it, the Ex 3 boundary applies. Note COUNT(*)=2 still: the two rows exist.

Result:

team	COUNT(*)	COUNT(pts)	SUM(pts)	AVG(pts)
X	2	2	30	15
Y	2	0	NULL	NULL

Verify: $10 + 20 = 30$ , $30/2 = 15$ ✓. Y's row appears (it has rows) but its SUM/AVG are NULL — grouping does not delete NULL-only groups, it summarises them. ✓

Worked example Ex 7 — DISTINCT inside COUNT and SUM ·

C7 Table orders:

id	region	amount
1	East	50
2	East	50
3	West	70
4	West	NULL

SELECT COUNT(DISTINCT region),
       COUNT(DISTINCT amount),
       SUM(DISTINCT amount)
FROM orders;

Forecast: duplicates and one NULL. What survives after de-duping?

DISTINCT region → {East, West} = 2. Why this step? DISTINCT collapses duplicates before counting (see DISTINCT keyword). Two unique regions.
DISTINCT amount → non-NULL uniques {50, 70} = 2. Why this step? The two 50's become one 50; NULL is skipped by COUNT anyway. So COUNT = 2.
SUM(DISTINCT amount) = 50 + 70 = 120. Why this step? Distinct values first ({50, 70}), then add. The duplicate 50 is not counted twice — this differs from plain SUM(amount) which would be $50 + 50 + 70 = 170$ .

Result: 2, 2, 120. Verify: plain SUM $= 170$ , distinct SUM $= 120$ ; difference $= 50$ (the removed duplicate). ✓

Worked example Ex 8 — Negative and zero values (signs) ·

C8 Table pnl (profit and loss):

id	delta
1	-30
2	0
3	50
4	-20

SELECT SUM(delta), AVG(delta), MIN(delta), MAX(delta)
FROM pnl;

Forecast: with negatives, is MIN the "biggest loss" or the "smallest number"? Guess all four.

SUM = -30 + 0 + 50 - 20 = 0. Why this step? SUM handles signs like ordinary arithmetic; gains and losses cancel.
AVG = 0 / 4 = 0. Why this step? No NULLs, so COUNT = 4; $0/4 = 0$ . (Zero is a value, not a NULL — it counts.)
MIN = -30. The most negative number is the smallest on the number line. Why this step? MIN means "leftmost on the number line", so a large loss (−30) is smaller than a small loss (−20).
MAX = 50. The largest gain.

Result: 0, 0, -30, 50. Verify: $- 30 + 0 + 50 - 20 = 0$ ✓; $0/4 = 0$ ✓; $- 30 < - 20 < 0 < 50$ so MIN $= - 30$ , MAX $= 50$ ✓. Note 0 is not skipped — only NULL is. ✓

Worked example Ex 9 — Real-world word problem ·

C9 Note on notation: the symbol ₹ just means "rupees", the currency of India — read it exactly like "$" (dollars); it plays no role in the maths, it only labels that the numbers are amounts of money.

"For the employees table, the finance team asks: 'What is our total payroll, and what would it look like if every unknown salary were treated as ₹0?'"

SELECT SUM(salary)                AS raw_total,
       SUM(COALESCE(salary, 0))   AS zero_filled_total,
       AVG(salary)                AS raw_avg,
       AVG(COALESCE(salary, 0))   AS zero_filled_avg
FROM employees;

Forecast: does forcing NULL→0 change the SUM? Does it change the AVG? (One of these answers is a surprise.)

raw_total = 600. NULL skipped ( $100 + 200 + 300$ ). Why this step? Plain SUM ignores the NULL row, so only the three known salaries are added.
zero_filled_total = 600. COALESCE(salary,0) turns row 4's NULL into 0; adding 0 changes nothing. Why this step? For SUM, replacing NULL with 0 has no numeric effect — 0 is the additive identity.
raw_avg = 600 / 3 = 200. Divides by 3 non-NULL values. Why this step? Plain AVG counts only the non-NULL salaries, so the denominator is 3 (rows 1, 2, 3). The NULL row is invisible to it, exactly as in Ex 2.
zero_filled_avg = 600 / 4 = 150. Now row 4 is a real 0, so COUNT becomes 4. Why this step? For AVG the COALESCE does change the answer: the once-invisible NULL becomes a counted 0, dragging the mean down. This is the whole reason COALESCE and NULLIF matters.

Result: raw_total=600, zero_filled_total=600, raw_avg=200, zero_filled_avg=150. Verify: SUM unchanged (600 = 600); AVG drops $200 \to 150$ because the denominator rose $3 \to 4$ . Units: ₹ throughout. ✓

Worked example Ex 10 — Exam twist: WHERE vs HAVING vs COALESCE ·

C10 "From employees, list each department's average salary, treating unknown salaries as 0, but only show departments whose (zero-filled) average is at least 100."

SELECT dept, AVG(COALESCE(salary, 0)) AS avg_sal
FROM employees
GROUP BY dept
HAVING AVG(COALESCE(salary, 0)) >= 100;

Forecast: compute each dept's zero-filled average, then decide who survives HAVING.

Group and zero-fill. A = {100, 200}, B = {300, 0} (row 4's NULL → 0). Why this step? The COALESCE runs per row before the aggregate folds the group, so by the time AVG runs there are no NULLs left in the column.
AVG per group: A $= (100 + 200) /2 = 150$ ; B $= (300 + 0) /2 = 150$ . Why this step? Both denominators are now 2, because zero-filling made every row a real value — so even B's once-NULL row is counted.
Apply HAVING >= 100. Both 150 ≥ 100, so both departments survive. Why this step? HAVING filters groups after aggregation — it can see AVG(...), which WHERE cannot, because in the pipeline $\dots \to aggregate \to HAVING \to \dots$ HAVING runs after the average is computed. (See SQL query execution order.)

Result:

dept	avg_sal
A	150
B	150

Verify: $(100 + 200) /2 = 150$ ✓; $(300 + 0) /2 = 150$ ✓; both $\geq 100$ ✓. Contrast with the parent's Ex 3 (no COALESCE), where B's raw AVG was 300 — zero-filling changed B from 300 to 150. ✓

Recall

Recall Boundary reflex — fill these instantly

SUM over an empty table ::: NULL COUNT(*) over an empty table ::: 0 AVG of an all-NULL column ::: NULL COUNT(col) of an all-NULL column ::: 0 MIN of a single-row table with value 42 ::: 42 MIN/MAX of a column with values 100,200,300 and one NULL ::: 100 and 300 — NULL is skipped, not treated as smallest Does 0 get skipped like NULL in SUM? ::: No — 0 is a real value; only NULL is skipped Does COALESCE(salary,0) change SUM? ::: No (adds 0); but it does change AVG (raises the count) SUM(DISTINCT amount) with amounts 50,50,70 ::: 120 (duplicate 50 removed)

Connections

Aggregate functions — COUNT, SUM, AVG, MIN, MAX — the parent concepts these drills exercise.
GROUP BY and HAVING — Ex 6 and Ex 10 depend on grouping and group-level filtering.
NULL handling in SQL — the skip-NULL rule that shapes Ex 2, 3, 6, 9.
COALESCE and NULLIF — the NULL→0 trick in Ex 9 and Ex 10.
SQL query execution order — why WHERE can't hold an aggregate (Ex 10).
DISTINCT keyword — de-duping inside COUNT/SUM (Ex 7).
Window functions — aggregates that keep every row instead of collapsing.

Worked examples — Aggregate functions — COUNT, SUM, AVG, MIN, MAX