Aggregate Functions, GROUP BY and HAVING

Aggregation questions test whether you truly understand SQL execution order. "WHERE vs HAVING" and "COUNT(*) vs COUNT(col)" are classic interview traps that catch candidates who guess.

Aggregate functions

COUNT, SUM, AVG, MIN, MAX collapse many rows into one value. COUNT(*) counts all rows including NULLs; COUNT(column) ignores NULLs; COUNT(DISTINCT col) counts unique non-NULL values. Aggregates (except COUNT(*)) skip NULLs.

GROUP BY and HAVING

GROUP BY partitions rows into groups so an aggregate is computed per group. HAVING filters groups after aggregation; WHERE filters rows before grouping.

Clause	Filters	Aggregates allowed?
WHERE	rows (pre-grouping)	no
HAVING	groups (post-aggregation)	yes

Logical execution order (crucial)

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

This is why you can't use a SELECT alias in WHERE (WHERE runs first) and why aggregate conditions must go in HAVING.

Exam Tricks & Tips

• 🎯 WHERE filters rows before grouping; HAVING filters groups after aggregation — you can't use an aggregate in WHERE.
• 🎯 COUNT(*) counts all rows (incl. NULLs); COUNT(col) ignores NULLs — the single most common aggregate trap.
• 🎯 Aggregate functions (except COUNT(*)) ignore NULL values — AVG divides by the count of non-NULLs.
• 🎯 Every non-aggregated column in the SELECT must appear in GROUP BY (standard SQL) — a frequent error.
• 🎯 Remember the execution order FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY — explains why SELECT aliases aren't visible in WHERE.
• 🎯 HAVING can reference aggregates (e.g. HAVING COUNT(*) > 5); WHERE cannot.
• ❌ Common mistake: putting an aggregate condition in WHERE (e.g. WHERE COUNT(*) > 5) — it must be in HAVING.

Expected pattern

"WHERE vs HAVING", "COUNT(*) vs COUNT(col)", "SQL logical execution order", "write a query with GROUP BY + HAVING", and "why can't I use an alias in WHERE?".

Quick recap

Aggregates (COUNT/SUM/AVG/MIN/MAX) collapse rows; COUNT(*) includes NULLs, COUNT(col) skips them. GROUP BY groups; WHERE filters rows pre-grouping, HAVING filters groups post-aggregation. Execution order: FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY. Aggregates go in HAVING, not WHERE.

Cover the answer, recall, then check. 12 cards on aggregation, GROUP BY and HAVING.

Q1. Name the five standard SQL aggregate functions.
A1. COUNT, SUM, AVG, MIN, and MAX.

Q2. Difference between COUNT() and COUNT(column)?
A2. COUNT() counts all rows including NULLs; COUNT(column) counts only non-NULL values in that column.

Q3. Difference between WHERE and HAVING?
A3. WHERE filters individual rows before grouping; HAVING filters groups after aggregation.

Q4. Can you use an aggregate function in a WHERE clause?
A4. No — aggregate conditions must go in HAVING; WHERE runs before aggregation.

Q5. What is the logical execution order of an SQL query?
A5. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Q6. Why can't you reference a SELECT alias in WHERE?
A6. WHERE is evaluated before SELECT in the logical order, so the alias doesn't exist yet.

Q7. Do aggregate functions include NULL values?
A7. No (except COUNT(*)) — SUM, AVG, MIN, MAX, and COUNT(col) ignore NULLs.

Q8. What must every non-aggregated SELECT column also appear in?
A8. The GROUP BY clause (in standard SQL).

Q9. What does GROUP BY do?
A9. Partitions rows into groups sharing the same grouping-column values, so aggregates are computed per group.

Q10. How does AVG handle NULLs?
A10. It ignores them — the average is the sum of non-NULL values divided by the count of non-NULL values.

Q11. What does COUNT(DISTINCT col) return?
A11. The number of distinct non-NULL values in that column.

Q12. Where would COUNT(*) > 5 go in a query?
A12. In the HAVING clause (filtering groups), not WHERE.

Aggregation turns many rows into summary numbers — counts, sums, averages per category. Mastering GROUP BY and the WHERE-vs-HAVING distinction is essential, both for real reporting queries and for exams.

Core idea: aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse a set of rows into one value. GROUP BY partitions rows into groups so aggregates are computed per group. WHERE filters rows before grouping; HAVING filters groups after aggregation.

How it works

Beginner — aggregates over all rows

SELECT COUNT(*), AVG(salary), MAX(salary) FROM Employee;

Without GROUP BY, the whole table is one group → one summary row.

Intermediate — GROUP BY partitions

SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_pay
FROM Employee
GROUP BY dept_id;                    -- one output row per department

Rule: every column in the SELECT list must be either inside an aggregate or in the GROUP BY. Selecting a non-grouped, non-aggregated column is an error (or, worse, silently arbitrary in lax databases).

Advanced — WHERE vs HAVING and the logical order

The clauses execute in a fixed logical order, which explains many quirks:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

• WHERE runs before grouping, so it filters individual rows and cannot reference aggregates.
• HAVING runs after grouping, so it filters groups and can use aggregates.
• Because SELECT runs after GROUP BY/HAVING, column aliases defined in SELECT aren't visible to WHERE/HAVING in standard SQL.

SELECT dept_id, AVG(salary) AS avg_pay
FROM Employee
WHERE status = 'active'              -- filter rows first
GROUP BY dept_id
HAVING AVG(salary) > 50000;          -- then filter groups

Worked example — count and threshold per department

Data: dept 1 has salaries {60k, 40k}; dept 2 has {90k}; dept 3 has {30k, 30k}.

SELECT dept_id, COUNT(*) AS n, AVG(salary) AS avg
FROM Employee GROUP BY dept_id HAVING AVG(salary) > 45000;

• Grouping: dept1 → (n=2, avg=50k), dept2 → (n=1, avg=90k), dept3 → (n=2, avg=30k).
• HAVING AVG > 45000 keeps dept1 (50k) and dept2 (90k); drops dept3 (30k).
  Result: two rows. Trying to write WHERE AVG(salary) > 45000 instead would be an error, because WHERE runs before the average exists — the exact point HAVING exists to solve.

Interview & exam relevance

Very common: "difference between WHERE and HAVING", "what must appear in GROUP BY", "how does COUNT(*) differ from COUNT(column)" (the latter skips NULLs), and predicting query output. The logical processing order is the key that unlocks most of these.

Tricks & gotchas

• WHERE = row filter (pre-group, no aggregates); HAVING = group filter (post-group, aggregates allowed).
• COUNT(*) counts rows; COUNT(col) counts non-NULL values of col; COUNT(DISTINCT col) counts distinct non-NULLs.
• Aggregates (except COUNT(*)) ignore NULLs — AVG(salary) divides by the count of non-NULL salaries.
• Mnemonic: "WHERE before grouping, HAVING after."

Worked Example

Problem: From Sales(region, amount) rows: (East,100),(East,NULL),(West,200),(West,300),(North,50), write a query to show each region's total and count of sales, keeping only regions whose total exceeds 150, ordered by total descending. Then explain why COUNT(amount) and COUNT(*) differ, and why WHERE cannot replace HAVING.

Solution:

SELECT region,
       SUM(amount)      AS total,
       COUNT(*)         AS n_rows,
       COUNT(amount)    AS n_nonnull
FROM Sales
GROUP BY region
HAVING SUM(amount) > 150
ORDER BY total DESC;

Step-by-step logical order: FROM → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY.
GROUP BY region forms three groups: East {100, NULL}, West {200, 300}, North {50}.
Aggregates per group:

• East: SUM = 100 (aggregates SKIP NULLs, so NULL is ignored, total = 100), COUNT(*) = 2, COUNT(amount) = 1.
• West: SUM = 500, COUNT(*) = 2, COUNT(amount) = 2.
• North: SUM = 50, COUNT(*) = 1, COUNT(amount) = 1.
  HAVING SUM(amount) > 150 keeps only groups whose total exceeds 150: West (500) qualifies; East (100) and North (50) are dropped.
  ORDER BY total DESC: only West remains.
  Result: (West, 500, 2, 2).

COUNT() vs COUNT(amount): COUNT() counts rows in the group (East = 2); COUNT(amount) counts non-NULL values of that column (East = 1, because one amount is NULL). This gap always signals NULLs.

Why not WHERE: WHERE filters individual rows BEFORE grouping and cannot reference an aggregate like SUM (the groups do not exist yet). HAVING filters GROUPS after aggregation, so conditions on SUM/COUNT/AVG must go in HAVING. Use WHERE to filter raw rows, HAVING to filter aggregated groups.

Answer: The query returns (West, total 500, n_rows 2, n_nonnull 2). SUM/COUNT ignore NULLs, so COUNT(*)=2 but COUNT(amount)=1 for East. HAVING (not WHERE) filters groups because only HAVING can reference aggregates.