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

The NULL rule — the heart of everything

Function	NULLs counted?
`COUNT(*)`	Yes — counts every row
`COUNT(col)`	No — only non-NULL values
`SUM/AVG/MIN/MAX(col)`	No — NULLs ignored

Worked examples

Table employees:

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

Worked example 1 — Simple totals

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

Result: 4, 3, 600, 200.

Why COUNT(*) = 4? Four rows exist.
Why COUNT(salary) = 3? Row 4's salary is NULL, skipped.
Why SUM = 600? $100+200+300$ — NULL skipped.
Why AVG = 200, not 150? AVG = SUM/COUNT(salary) = $600/3 = 200$ . It divides by 3, not 4, because NULL is invisible. This is the trap.

Worked example 2 — Per-group aggregates with GROUP BY

SELECT dept, COUNT(*), AVG(salary)
FROM employees
GROUP BY dept;

dept	COUNT(*)	AVG(salary)
A	2	150
B	2	300

Why this step? GROUP BY dept splits the table into mini-tables (A-rows, B-rows). The aggregate runs inside each group.
Why is B's AVG = 300, not 150? B has values {300, NULL}. SUM=300, COUNT(salary)=1, so $300/1=300$ .

Worked example 3 — Filtering aggregates with HAVING

SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
HAVING AVG(salary) > 200;

Returns only dept B (300).

Why HAVING, not WHERE? WHERE filters rows before grouping; it cannot see an aggregate. HAVING filters groups after aggregation. Use HAVING whenever the condition involves COUNT/SUM/AVG/etc.

Worked example 4 — COUNT(DISTINCT ...) and MIN/MAX

SELECT COUNT(DISTINCT dept), MIN(salary), MAX(salary)
FROM employees;

Result: 2, 100, 300. DISTINCT removes duplicates first (A, B → 2 unique departments).

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

Common mistakes (steel-manned)

Order of execution (memorise this!)

Flashcards

What does COUNT() count that COUNT(col) does not? ::: Rows where col is NULL — COUNT() counts all rows, COUNT(col) counts only non-NULL values.

Why is AVG(salary) often higher than SUM/total_rows?

AVG divides by the number of non-NULL values, not the total row count; NULLs are skipped.

Which clause filters groups by an aggregate condition?

HAVING (WHERE runs before aggregation and cannot see aggregates).

What does SUM return over zero non-NULL rows?

NULL, not 0 (COUNT returns 0 in that case).

Logical execution order of a grouped query?

FROM → WHERE → GROUP BY → aggregate → HAVING → SELECT → ORDER BY.

Formula for AVG from first principles?

AVG = (sum of non-NULL values) / (count of non-NULL values).

How to make AVG treat NULL as 0?

AVG(COALESCE(col, 0)) so NULLs become 0 and are counted.

What does COUNT(DISTINCT col) return?

The number of distinct non-NULL values in col.

Recall Feynman: explain to a 12-year-old

Imagine a list of test scores on a chalkboard. Instead of reading every score, your teacher asks 5 quick questions: How many tests? (COUNT), Add them all up (SUM), What's the typical score? (AVG), Lowest? (MIN), Highest? (MAX). Some kids were absent and left a blank — for adding and averaging you ignore the blanks, but if you just count "how many seats are there" you still count the empty desk (that's COUNT(*)). GROUP BY is like sorting kids into Team A and Team B first, then asking those 5 questions for each team separately.

Connections

GROUP BY and HAVING — aggregates almost always pair with grouping.
NULL handling in SQL — the skip-NULL rule drives every result here.
COALESCE and NULLIF — turning NULL into 0 for clean sums/averages.
SQL query execution order — why WHERE ≠ HAVING.
Window functions — aggregates that don't collapse rows (OVER()).
DISTINCT keyword — used inside COUNT to dedupe.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, aggregate functions ka funda simple hai: ek poori column ki values ko le kar wo ek single answer nikaal dete hain. COUNT batata hai kitni rows hain, SUM total karta hai, AVG average deta hai, MIN sabse chhoti value, MAX sabse badi. Jab boss poochhe "kitne employees hain?" ya "total salary kitni hai?" — tab raw data nahi, ye summary chahiye hoti hai. Isiliye ye functions banaye gaye hain.

Sabse important baat — NULL ka rule. NULL ka matlab "value pata hi nahi". To SUM, AVG, MIN, MAX in sabne NULL ko ignore kar diya, kyunki unknown ko add karna galat hoga. Bas ek exception: COUNT(*) har row ginta hai chahe value blank ho, kyunki row to exist karti hi hai. Yahi wajah hai ki AVG kabhi-kabhi tumhare expected se zyada aata hai — kyunki AVG = SUM / (non-NULL count), total rows se divide nahi karta. Yaad rakho: "stars count empties, names don't."

GROUP BY ka idea ye hai ki pehle table ko teams me baant do (jaise dept A aur dept B), phir har team ke andar alag-alag aggregate calculate karo. Aur jab tumhe group ko filter karna ho aggregate ke basis par (jaise sirf wo dept jiska average > 200 ho), tab WHERE nahi, HAVING use karo — kyunki WHERE grouping se pehle chalta hai aur aggregate ko dekh hi nahi pata. Execution order yaad rakho: FROM → WHERE → GROUP BY → aggregate → HAVING → SELECT → ORDER BY. Ye ek line poore confusion ko solve kar deti hai. Exam aur real projects dono me ye topic bahut frequently aata hai, isiliye NULL trap aur HAVING vs WHERE clear hona must hai.

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

What each function does

The NULL rule — the heart of everything

Worked examples

Common mistakes (steel-manned)

Order of execution (memorise this!)

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections