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

4.4.8 · Coding › Databases

NULL rule — sab kuch isi par tikaa hai

Function	NULLs count hote hain?
`COUNT(*)`	Haan — har row count hoti hai
`COUNT(col)`	Nahi — sirf non-NULL values
`SUM/AVG/MIN/MAX(col)`	Nahi — NULLs ignore hote hain

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.

COUNT(*) = 4 kyun? Chaar rows exist karti hain.
COUNT(salary) = 3 kyun? Row 4 ki salary NULL hai, skip ho gayi.
SUM = 600 kyun? $100 + 200 + 300$ — NULL skip.
AVG = 200, 150 nahi kyun? AVG = SUM/COUNT(salary) = $600/3 = 200$ . Yeh 3 se divide karta hai, 4 se nahi, kyunki NULL invisible hai. Yahi trap hai.

Worked example 2 — GROUP BY ke saath per-group aggregates

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

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

Yeh step kyun? GROUP BY dept table ko mini-tables mein split karta hai (A-rows, B-rows). Aggregate har group ke andar run hoti hai.
B ka AVG = 300 kyun, 150 nahi? B ki values hain {300, NULL}. SUM=300, COUNT(salary)=1, toh $300/1 = 300$ .

Worked example 3 — HAVING se aggregates filter karna

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

Sirf dept B (300) return karta hai.

HAVING kyun, WHERE nahi? WHERE grouping se pehle rows filter karta hai; woh ek aggregate nahi dekh sakta. HAVING aggregation ke baad groups filter karta hai. HAVING tab use karo jab condition mein COUNT/SUM/AVG/etc. ho.

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

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

Result: 2, 100, 300. DISTINCT pehle duplicates hata deta hai (A, B → 2 unique departments).

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

Common mistakes (steel-manned)

Order of execution (yeh zaroor yaad karo!)

Flashcards

COUNT() kya count karta hai jo COUNT(col) nahi karta? ::: Woh rows jahan col NULL hai — COUNT() saari rows count karta hai, COUNT(col) sirf non-NULL values count karta hai.

AVG(salary) aksar SUM/total_rows se zyada kyun hota hai?

AVG non-NULL values ki count se divide karta hai, total row count se nahi; NULLs skip ho jaate hain.

Kaunsa clause groups ko aggregate condition se filter karta hai?

HAVING (WHERE aggregation se pehle run hota hai aur aggregates nahi dekh sakta).

SUM zero non-NULL rows par kya return karta hai?

NULL, 0 nahi (COUNT us case mein 0 return karta hai).

Ek grouped query ka logical execution order?

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

First principles se AVG ka formula?

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

AVG ko NULL ko 0 treat kaise karwayein?

AVG(COALESCE(col, 0)) taaki NULLs 0 ban jayein aur count mein aayein.

COUNT(DISTINCT col) kya return karta hai?

col mein distinct non-NULL values ki count.

Recall Feynman: ek 12-saal ke bachche ko explain karo

Socho ek chalkboard par test scores ki list hai. Har score padhne ki jagah, tumhari teacher 5 quick questions poochti hai: Kitne tests? (COUNT), Sab jodo (SUM), Typical score kya hai? (AVG), Sabse kam? (MIN), Sabse zyada? (MAX). Kuch bachche absent the aur unhone blank chhod diya — jodne aur average nikalne ke liye tum blanks ignore karte ho, lekin agar tum sirf "kitni seats hain" count kar rahe ho toh phir bhi empty desk count hoti hai (yahi COUNT(*) hai). GROUP BY aisa hai jaise bachcon ko pehle Team A aur Team B mein sort karo, phir har team ke liye alag se woh 5 questions poochho.

Connections

GROUP BY and HAVING — aggregates almost hamesha grouping ke saath aati hain.
NULL handling in SQL — skip-NULL rule yahan har result drive karta hai.
COALESCE and NULLIF — NULL ko 0 mein badalna clean sums/averages ke liye.
SQL query execution order — isliye WHERE ≠ HAVING.
Window functions — aggregates jo rows collapse nahi karti (OVER()).
DISTINCT keyword — COUNT ke andar dedupe ke liye use hota hai.