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

Before you can trust a single line of the parent note, you need to own every word and symbol it throws at you. Below, each idea is built from nothing, given a picture, and justified — why does the topic even need this?

1. A table, a row, and a column

Why the topic needs this: aggregate functions do not work on a single number and they do not work on a whole row. They work on a column — a vertical list. When the parent writes SUM(salary), the word salary names the column (the blue vertical strip in the figure), and SUM walks down that strip. If you don't see "column = vertical list", none of the functions make sense.

2. Values, and the strange blank called NULL

Why the topic needs this: the entire "AVG divides by 3, not 4" trap and the "SUM of all-NULL is NULL, not 0" mistake come directly from NULL being a skipped non-value. See NULL handling in SQL for the full rulebook.

3. The SELECT statement — asking a question

Why the topic needs this: aggregates live inside the SELECT part. SELECT SUM(salary) FROM employees still says "give me... out of...", but now the "what you want" is a collapsed number instead of the raw list.

4. The function-call notation NAME(x)

• COUNT(*) — the special input * means "the whole row", so it counts rows, blanks and all.
• COUNT(salary) — input is the salary column, so it counts only non-NULL salaries.

Why the topic needs this: every one of the five functions — COUNT, SUM, AVG, MIN, MAX — uses this NAME(x) shape. Once you see it as "funnel with an input", the parent's whole table of behaviours is just "what does each funnel do to blanks?".

5. Summation notation $\sum$ and the fraction bar

The parent's AVG formula uses two pieces of maths notation. Let's earn both.

Now the average formula reads cleanly: $\text{AVG}(x)=\frac{{\sum }_{i=1}^n{x}_i}{n}=\frac{\text{total of the values}}{\text{how many values}}$

The horizontal fraction bar means divide: the total (top) split fairly among $n$ shares (bottom). That's why AVG is literally SUM ÷ COUNT — and why, when NULLs shrink $n$ from 4 to 3, the average jumps.

6. Comparing values — MIN, MAX, and the > sign

Why the topic needs this: MIN/MAX are just "look down the sorted column and grab an end". And > is what powers HAVING AVG(salary) > 200 — a test that keeps or drops each group.

7. DISTINCT — throwing away duplicates

Why the topic needs this: COUNT(DISTINCT dept) first squashes duplicates (4 → 2), then counts. That's how the parent's Example 4 gets 2. See DISTINCT keyword.

8. GROUP BY — sorting rows into piles first

Why the topic needs this: it's the difference between "average salary of the company" and "average salary of each department". The deep mechanics of grouping and the HAVING filter live in GROUP BY and HAVING.

9. The order things actually happen

You don't need to master this yet — just know it exists and that it explains the classic "why can't I put an aggregate in WHERE?" puzzle: WHERE runs before the aggregate is computed, so the total doesn't exist yet. Full story in SQL query execution order.

Prerequisite map

Equipment checklist

Cover the right side and test yourself. If any answer is fuzzy, re-read that section before the parent note.