Aggregate functions — COUNT, SUM, AVG, MIN, MAX: Visual walkthrough

We use the parent's tiny table the whole way so every number is checkable:

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

Step 1 — A column is just a vertical list of boxes

WHAT. Before any function runs, picture the salary column alone: four stacked boxes. Three hold numbers ( $100, 200, 300$ ); one is a shaded "unknown" box (NULL).

WHY start here. Every aggregate — COUNT, SUM, AVG, MIN, MAX — eats exactly this list and spits out exactly one value. If you can see the list clearly, you can see what each function does to it. Nothing else is needed yet: no formulas, no symbols.

PICTURE. Look at the four boxes. The blue ones are real numbers. The red hatched box is NULL — it means "we genuinely do not know this value", not "zero".

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

Step 2 — COUNT is a walk that tallies boxes

WHAT. Walk down the column top to bottom. Keep a tally $n$ . Two different rules give two different tallies:

$COUNT (*)$ — tick every box you pass: $n = 4$ .
$COUNT (salary)$ — tick only boxes that hold a real value: $n = 3$ (skip the shaded one).

WHY two rules. A row existing is a fact even if its cell is empty — so $COUNT (*)$ counts the desk whether or not a student sits there. But $COUNT (salary)$ asks "how many known salaries?", so it must skip the unknown.

PICTURE. The green arrow is the walk. Above each box sits the running tally after visiting it. Watch the two counters diverge at box 4: the star-counter climbs to 4, the salary-counter stays at 3.

tick every box COUNT (*) = 4 tick only known boxes COUNT (salary) = 3

$COUNT (*)$ — the $*$ means "the whole row", so NULL never matters.
$COUNT (salary)$ — names a column, so it obeys the skip-NULL rule.

Step 3 — SUM is the same walk, but we add instead of tally

WHAT. Walk again. This time keep a running total $S$ , starting at $0$ . At each box: if it holds a number, add it to $S$ ; if it's the shaded NULL box, add nothing and move on.

S : 0 + 100 100 + 200 300 + 300 600 skip NULL 600

WHY skip, not add-zero. Adding NULL would be claiming "the unknown salary is 0" — a lie about the data. SQL refuses to invent that, so the total stays at $600$ . (If you genuinely want NULL-as-zero, that's a choice you make with COALESCE and NULLIF, not a default.)

PICTURE. The yellow bar grows step by step. At box 4 the bar does not grow — the shaded box contributes a flat segment.

running total S = three known values 100 + 200 + 300 + NULL skipped (nothing) = 600

Step 4 — AVG is not a new walk: it reuses $S$ and $n$

WHAT. Here is the central result. The database already carried two pencilled numbers down the column: the sum $S$ and the count $n$ of values it actually added. The average is just their ratio.

AVG (x) = \frac{i = 1 \sum n x _{i}}{n} = \frac{S}{n}

$\sum_{i = 1}^{n} x_{i}$ — "add up $x_{1}$ through $x_{n}$ ", which is exactly the total $S$ we built in Step 3.
$n$ — the count of non-NULL values, exactly $COUNT (salary)$ from Step 2. Not $COUNT (*)$ .
The fraction bar — "fair share": split the total equally among the boxes that had a value.

WHY $n = 3$ and not $4$ . The NULL box never contributed to $S$ (Step 3), so it has no right to a share of it. Dividing by 4 would spread $600$ across a box that put in nothing — deflating the true average. So:

AVG (salary) = \frac{S}{n} = \frac{600}{3} = 200 (not 600/4 = 150)

PICTURE. Left: the yellow $S = 600$ bar. Below: the three blue boxes it came from. The red "phantom" box shows the wrong denominator (4) and the wrong answer (150) crossed out.

Step 5 — MIN and MAX: the same walk, keeping the champion

WHAT. One more pass. Keep one box in your hand — the current champion. For MIN, swap whenever you see something smaller; for MAX, whenever you see something bigger. Skip the shaded NULL (an unknown can't win or lose a comparison).

MIN : 100 \to 100 \to 100 \to (skip) = 100 MAX : 100 \to 200 \to 300 \to (skip) = 300

WHY skip NULL again. "Is unknown $<$ 100?" has no honest answer, so NULL never becomes the champion and never displaces one. The winner is chosen only among known values.

PICTURE. The number line holds the three known salaries. A green marker slides to the leftmost (MIN) and a red marker to the rightmost (MAX); the NULL floats off the line, disconnected.

Step 6 — The degenerate case: an all-NULL (or empty) column

WHAT. Suppose every box in the column is shaded — dept B, if it had only row 4. Now walk each function:

Function	Walk result	Returns
$COUNT (*)$	one box exists	$1$
$COUNT (salary)$	zero known values	$0$
$SUM (salary)$	nothing added	NULL
$AVG (salary)$	$S / n = ? /0$ — undefined	NULL
$MIN / MAX$	no champion ever chosen	NULL

WHY SUM returns NULL, not 0. This surprises everyone. But look at the walk: SUM had no non-NULL rows to add. It never even started adding. Returning $0$ would claim "the total of these unknowns is zero" — again inventing data. And AVG would need $S / n = something /0$ , which is undefined, so it too yields NULL rather than dividing by zero.

PICTURE. Three shaded boxes feeding into a decision fork: COUNT paths land on a number, SUM/AVG/MIN/MAX paths all land on a NULL bubble.

Step 7 — GROUP BY: run the whole walk once per mini-table

WHAT. GROUP BY dept doesn't change any function — it just slices the column into stacks before the walk. Dept A = boxes ${100, 200}$ ; dept B = boxes ${300, NULL}$ . Then Steps 2–5 run inside each stack.

A : \frac{100 + 200}{2} = 150 B : \frac{300 + (skip)}{1} = \frac{300}{1} = 300

WHY B's average is 300, not 150. Inside stack B the walk finds one known value ( $300$ ) and one NULL. So $S = 300$ and $n = 1$ — dividing by 1, not 2. Same skip-NULL rule as Step 4, applied per group.

PICTURE. The column splits into two shorter columns; each runs its own tiny SUM/COUNT/AVG, producing one summary row apiece.

Recall Why HAVING and not WHERE, in one line

The average doesn't exist until after the per-group walk finishes. WHERE runs before grouping (Step 7 hasn't happened yet), so it can't see $AVG$ ; HAVING runs after, so it can. Full order in SQL query execution order. Details in GROUP BY and HAVING.

The one-picture summary

WHAT. One column, one walk, five pencils. Every aggregate is the same downward pass keeping a different scrap of paper: a tally (COUNT), a running total (SUM), the pair $S$ -and- $n$ (AVG), or the current champion (MIN/MAX) — and every one of them steps over the shaded NULL box, except $COUNT (*)$ which counts the box itself.

Recall Feynman: tell it to a 12-year-old

Imagine a stack of index cards, one salary per card, and one card left blank because we never found out that person's pay. You walk down the stack once, top to bottom, holding a few scraps of paper. On one scrap you make a tick for every card — that's $COUNT (*)$ , and even the blank card gets a tick because a card is there. On another scrap you tick only cards with a number — that's $COUNT (salary)$ , and the blank card gets skipped. On a third scrap you keep a running sum, adding each number but adding nothing for the blank — that's SUM. The average? You don't do a new walk: you just take your running sum and split it among the cards that actually had a number — three cards, so $600 \div 3 = 200$ , not $600 \div 4 = 150$ . The blank card never put money in, so it gets no share. And if every card were blank, you'd have nothing to add and nothing to split, so SUM and AVG shrug and say "unknown" (NULL) — only the tick-counter can still say "zero".

Connections

NULL handling in SQL — the shaded box that drives every "3 not 4".
GROUP BY and HAVING — Step 7: run the same walk per mini-table.
COALESCE and NULLIF — force NULL-as-0 before SUM/AVG.
SQL query execution order — why WHERE can't see the average but HAVING can.
Window functions — the same walk that doesn't collapse the rows.
DISTINCT keyword — de-dupe the column before COUNT.
Yeh Hinglish mein padho →