SQL Roadmap/Aggregation & Grouping

GROUP BY, HAVING & Aggregate Functions

COUNT, SUM, AVG, MIN, MAX collapse a group of rows into one summary value per group — and HAVING, not WHERE, is how you filter on the result of that collapse.

!!!2/5Theory: 25m4 problems

The five core aggregate functions

COUNT, SUM, AVG, MIN, MAX collapse a set of rows into a single value. Used without GROUP BY, they collapse the entire table into one row:

SELECT COUNT(*) AS total_orders, AVG(amount) AS avg_amount FROM orders;

GROUP BY changes the unit of collapse from "the whole table" to "each distinct group of values":

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent FROM orders GROUP BY customer_id;

Every column in the SELECT list that isn't wrapped in an aggregate function must appear in GROUP BY — this is a hard rule in standard SQL and strict-mode MySQL/PostgreSQL (older, lenient MySQL configurations silently pick an arbitrary row's value for ungrouped columns, which is almost never what you want and is worth knowing about purely so you recognize the bug if you ever see it).

COUNT(*) vs. COUNT(column) vs. COUNT(DISTINCT column)

This distinction comes up constantly and is worth having completely automatic:

  • COUNT(*) counts every row in the group, including rows where every column is NULL.
  • COUNT(column) counts only rows where column is not NULL — aggregate functions ignore NULLs by default (this applies to SUM, AVG, MIN, MAX too, not just COUNT).
  • COUNT(DISTINCT column) counts unique non-NULL values of column within the group.

A frequent bug: using COUNT(some_column) when the intent was "count all rows in this group," and getting a smaller number than expected because some_column happens to be NULL on some rows. When in doubt about intent, COUNT(*) is almost always the safer default for "how many rows."

HAVING vs. WHERE: filtering before vs. after the collapse

This is the single most important distinction in this subtopic. WHERE filters individual rows before grouping happens; HAVING filters groups after aggregation has already collapsed them:

SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE status = 'completed' -- row-level filter, applied first GROUP BY customer_id HAVING COUNT(*) > 5; -- group-level filter, applied after aggregation

You cannot reference an aggregate function in WHERE (WHERE COUNT(*) > 5 is a syntax/semantic error on every mainstream engine) precisely because WHERE runs before aggregates are computed — there's no COUNT(*) yet at the point WHERE is evaluated. Conversely, you generally can reference a non-aggregated column in HAVING, but there's rarely a reason to — if a condition doesn't depend on an aggregate, it belongs in WHERE, both for correctness of intent and because filtering rows before grouping is typically cheaper than filtering after.

Logical order of operations

The clauses execute in this order, which does not match the order you type them in:

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

This explains several otherwise-confusing rules: why WHERE can't see aggregates (they don't exist yet), why HAVING can see them (GROUP BY already ran), and why ORDER BY can reference a column alias defined in SELECT (on most engines) even though WHERE cannot (it runs before SELECT assigns that alias).

Why interviewers care

HAVING vs. WHERE is a fast, low-effort way to check whether a candidate understands SQL's actual execution model or has just memorized "clauses go in this order." A candidate who instinctively puts a row-level filter in HAVING "because it comes after WHERE in my mental checklist" — instead of because the condition genuinely needs an aggregate — is signaling pattern-matching rather than understanding, and it usually costs a small but real amount of query performance too (filtering fewer rows earlier is cheaper than aggregating everything and discarding groups afterward).

Pitfalls and interview gotchas

  • Putting a row-level condition in HAVING instead of WHERE. Works, but signals shaky understanding of why the two clauses exist separately — and is measurably slower on large tables.
  • Selecting an ungrouped, non-aggregated column and getting away with it on a lenient engine, then having the query fail (correctly) on a strict one.
  • Confusing COUNT(column) with COUNT(*) when a column can be NULL.
  • Forgetting that AVG, SUM, MIN, MAX all silently skip NULL values — AVG in particular divides by the count of non-NULL rows, not the total row count, which can be a surprising (and sometimes exactly desired) behavior depending on the question.

Where this goes next

Advanced Aggregation & Pivoting (next) builds directly on GROUP BY by nesting a CASE expression inside an aggregate function — the technique that lets you compute several different conditional totals in a single grouped pass, and the standard portable way to reshape rows into columns.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked

Optional Practice (Extra Reps)

For once you've cleared the main set above and want more reps on this pattern. These don't count toward the roadmap's progress stats — solve them purely for your own benefit.