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

WHAT the question asks: "how many rows exist" — existence of a row, regardless of whether its amount cell is filled. WHY COUNT(*): the star means "count the rows themselves." COUNT(amount) would drop the two NULL-amount rows, giving the wrong answer to this question. Answer: COUNT(*) → there are 7 rows.

WHAT we do: scan the amount column, ignoring the two NULLs (rows 3 and 6, which are "unknown" — you cannot say an unknown is the smallest or largest honestly). Remaining values: $\{100,200,300,300,50\}$. MIN = smallest = 50. MAX = largest = 300.

• Filtering groups by an aggregate → ==HAVING==.
• Filtering individual rows before grouping → ==WHERE==. WHY the split exists: in the pipeline $\text{WHERE}\to \text{GROUP BY}\to \text{aggregate}\to \text{HAVING}$, WHERE runs before the aggregate exists, so it literally cannot see SUM(amount). HAVING runs after, so it can. (See SQL query execution order.)

WHAT we do, step by step:

• COUNT(*) counts rows → 7.
• COUNT(amount) counts non-NULL amounts → rows 3 and 6 dropped → 5.
• SUM(amount) adds non-NULL values: $100+200+300+300+50=950$ → 950.
• AVG(amount) = SUM ÷ COUNT(amount) = $950/5=190$ → 190. WHY divide by 5, not 7? AVG divides by the count of non-NULL values. The two blanks are invisible. If you wrongly divide by 7 you get $950/7\approx 135.7$ — that is the classic error.

WHAT COALESCE(amount,0) does: it replaces each NULL with 0 before the aggregate sees it (see COALESCE and NULLIF). So the two blanks become real zero values. Now the column is $\{100,200,0,300,300,0,50\}$ — 7 non-NULL values. SUM is unchanged ($0$ adds nothing) = $950$, but the count is now 7. AVG = $950/7=135.714\dots \approx 135.71$. WHY different: turning NULL into 0 makes those rows count toward the denominator. Use this only when "missing" genuinely means "zero sales".

WHAT GROUP BY region does: it splits sales into mini-tables — one per distinct region — then runs the aggregate inside each.

WHY East's AVG = 150 not 100: East has 3 rows but only 2 non-NULL amounts ($\{100,200\}$), so $300/2=150$. Row 3's NULL is skipped in both SUM and COUNT.

WHAT DISTINCT does inside COUNT: it removes duplicates first, then counts what remains — and NULL is still excluded (see DISTINCT keyword).

• rep values: Alice, Bob, Alice, Carol, Carol, Dan, Eve → distinct set = {Alice, Bob, Carol, Dan, Eve} → 5.
• amount values (NULLs dropped): 100, 200, 300, 300, 50 → distinct set = {100, 200, 300, 50} → 4 (the two 300s collapse to one). WHY 4 not 5: the duplicate 300 (rows 4 and 5) is counted once by DISTINCT.

North has one row (id 7, amount 50): SUM = 50, AVG = 50, MIN = 50, COUNT(amount) = 1.

South (zero non-NULL rows) — the important case:

• SUM(amount) → NULL (there is nothing to add; the empty sum returns NULL, not 0, in SQL).
• AVG(amount) → NULL ($0/0$ is undefined, so SQL returns NULL).
• MIN(amount) → NULL (no value to be smallest).
• COUNT(amount) → 0 (COUNT is the one that returns a number, never NULL). WHY this asymmetry: COUNT answers "how many?" — the honest answer to an empty set is zero. SUM/AVG/MIN/MAX answer "what value?" — and there is no value, so NULL ("unknown") is the honest answer. To force 0, use COALESCE(SUM(amount), 0).

WHAT we compare per region: COUNT(*) (rows) vs COUNT(DISTINCT rep) (unique reps).

WHY it matters: if these two counts differ, a rep appears more than once in that region — a useful data-quality signal.

SELECT region, AVG(amount) AS avg_amt
FROM sales
GROUP BY region
HAVING AVG(amount) > 100;

WHY HAVING not WHERE: the condition uses an aggregate (AVG), which does not exist until after grouping. WHERE runs earlier and would error. From L2.3 the averages were East 150, West 300, North 50. Keep those with average > 100:

North (50) is dropped.

SELECT region, SUM(amount) AS big_total
FROM sales
WHERE amount >= 100
GROUP BY region;

WHY WHERE here (not HAVING): we filter individual rows by a plain column condition before grouping. WHERE is the correct, earlier stage. Also note: WHERE amount >= 100 drops NULL amounts automatically — a comparison with NULL is never true (see NULL handling in SQL). So rows 3, 6 (NULL) and row 7 (50) are excluded. Surviving rows: East {100,200}, West {300,300}.

(North has no row ≥ 100, so it disappears from the result entirely.)

Two separate errors:

1. WHERE a > 100 references an aggregate condition — but WHERE runs before aggregation, so AVG(amount) isn't computed yet. → must be HAVING.
2. It also references the alias a — but SELECT (where aliases are born) runs after WHERE in the pipeline $\dots \to \text{HAVING}\to \text{SELECT}\to \dots$, so WHERE cannot see a even if it were a plain column. Fix:

SELECT region, AVG(amount) AS a
FROM sales
GROUP BY region
HAVING AVG(amount) > 100;

Result is the same as L4.1: East 150, West 300.

Work region by region, ORDER BY region gives alphabetical order East, North, West.

Key checks: East has 3 rows but 2 non-NULL amounts → amt_cnt=2, a = 300/2 = 150. Since no region here is all-NULL, s0 equals s everywhere (COALESCE only changes an all-NULL group's NULL into 0).

• SUM(amount) = 950, COUNT(*) = 7 → $950/7=135.714\dots \approx 135.71$. This is "total divided by all rows", treating the two NULLs as if they were 0-value sales.
• AVG(amount) = 950/5 = 190$. This divides by the **5 known** sales. **WHICH is right for "average among known sales":** AVG(amount)` (190), because it ignores the unknowns. The $135.71$ figure silently assumes the blanks are zero — only valid if a missing amount truly means "no sale of value 0".

SELECT rep,
       COUNT(*)                 AS n_rows,
       COALESCE(SUM(amount), 0) AS total
FROM sales
GROUP BY rep
HAVING COUNT(*) > 1
ORDER BY rep;

WHY HAVING COUNT(*) > 1: we filter groups by row count — an aggregate condition, so it belongs in HAVING. Group by rep:

• Alice: rows 1,3 → amounts {100, NULL} → n=2, SUM=100.
• Bob: row 2 → n=1 → dropped (not > 1).
• Carol: rows 4,5 → {300,300} → n=2, SUM=600.
• Dan: row 6 → n=1 → dropped.
• Eve: row 7 → n=1 → dropped.

Alice's total is 100 (her NULL row is skipped by SUM); COALESCE wasn't needed here since she has one non-NULL amount, but it protects any rep who happened to be all-NULL.