HAVING

HAVING: a filter for groups, not rows

WHERE filters individual rows. HAVING filters the groups that GROUP BY produces. The two clauses look similar and are easy to confuse, but they run at different moments and see different things. WHERE runs first, on raw rows, before any grouping or aggregation has happened — so it cannot see a COUNT or a SUM, because those values do not exist yet. HAVING runs after grouping, once every group has been collapsed into one summary row, so it can test an aggregate.

That single fact explains almost everything about HAVING: when you use it, why a condition belongs in one clause versus the other, and the cryptic errors you hit when you put an aggregate in the wrong place.

The logical phase order

SQL is declarative: you describe the result you want, not the steps. But the engine evaluates the clauses in a fixed logical order, and knowing it removes the mystery. For a grouped query the order is:

1. FROM — pick the source rows.
2. WHERE — drop individual rows that fail a per-row condition. No aggregates exist here yet.
3. GROUP BY — bucket the surviving rows into groups; each group collapses to one row.
4. HAVING — drop whole groups that fail a condition. Aggregates are available.
5. SELECT — compute the output columns (including aggregates and column aliases).
6. ORDER BY / LIMIT — sort and trim the final rows.

Read WHERE and HAVING as two filters at two different stages of an assembly line: one screens parts before assembly, the other screens finished units after assembly. You would not inspect a finished car's fuel economy while parts are still on the belt, and you would not re-inspect a single bolt after the car is built.

Syntax and the canonical use

The clause attaches to a grouped query and tests a condition over each group:

SELECT   course, COUNT(student_id) AS total_students
FROM     Students
WHERE    score > 79
GROUP BY course
HAVING   COUNT(student_id) > 1;

Read it as: keep only rows whose score is above 79, bucket the survivors by course, then keep only those courses that contain more than one such student.

Heads-up on a bug in the original page: the original lesson filtered on score > 79 in one code block but on marks > 79 in another, against a single Students table that has only one of those columns. That is a real error — a query referencing a non-existent column raises Unknown column 'marks' in 'where clause' (MySQL) or an equivalent column does not exist error elsewhere. Pick one column name and use it consistently; this lesson uses score throughout.

Tracing the query, row by row

Take the seven-row Students table above and walk the pipeline by hand.

1. WHERE score > 79. Seven rows in; two (Cara at 62, Gus at 54) fall below 80, so five rows survive: Ann, Ben (Math), Dan, Eve (English), Fay (History).
2. GROUP BY course. The five survivors bucket into three groups: Math (Ann, Ben), English (Dan, Eve), History (Fay).
3. Aggregate. COUNT(student_id) per group: Math = 2, English = 2, History = 1.
4. HAVING COUNT(student_id) > 1. Math (2) and English (2) pass; History (1) is dropped because Fay is its only above-79 student.

The result:

Why WHERE before HAVING matters here

The placement of score > 79 is doing real work, and it is worth seeing exactly what would change without it. COUNT(student_id) counts every non-null student_id in the group regardless of score, so the WHERE is what keeps low scorers out of the count in the first place.

With the WHERE in place, History's group contains only Fay (Gus was filtered out before grouping), so its count is 1 and it fails HAVING COUNT(student_id) > 1. Drop the WHERE and History's group becomes Fay and Gus; COUNT(student_id) is now 2 — Gus's score of 54 does nothing to exclude him from the count — so History would pass HAVING > 1 and wrongly appear in the result. That is precisely why the per-row score test belongs in WHERE: HAVING filters groups but cannot un-count a row that was already counted.

WHERE vs HAVING: which condition goes where

The rule follows directly from the phase order: if a condition can be decided from a single row, put it in WHERE; if it needs an aggregate over a group, put it in HAVING.

Prefer WHERE whenever a condition is per-row: it shrinks the data before the more expensive grouping step, and it keeps the intent clear.

The errors you will actually hit

Putting an aggregate in WHERE fails, because at WHERE-time no groups exist yet:

-- WRONG: aggregate in WHERE
SELECT course
FROM Students
WHERE COUNT(student_id) > 1   -- error
GROUP BY course;

MySQL rejects this with ERROR 1111 (HY000): Invalid use of group function. PostgreSQL says aggregate functions are not allowed in WHERE. Different wording, same cause: you asked for a group summary before any group had been formed. Move the test to HAVING and it works.

The mirror-image mistake — a non-aggregate, non-grouped column in HAVING or SELECT — also errors in strict modes, because such a column is not well-defined per group.

Sharp edges worth knowing

• HAVING does not require an aggregate in SELECT. You can filter on an aggregate you never display, e.g. SELECT course FROM Students GROUP BY course HAVING AVG(score) >= 85; returns just the course names. HAVING sees aggregates whether or not they appear in the output.
• HAVING on a SELECT alias is not portable. Logically HAVING runs before SELECT, so the alias should not exist yet. MySQL leniently allows HAVING total_students > 1, but standard SQL and several engines (including older or stricter configurations) require you to repeat the aggregate: HAVING COUNT(student_id) > 1. Repeating the expression is the portable choice, which is why this lesson writes it out.
• COUNT(column) versus COUNT(*). COUNT(*) counts rows; COUNT(student_id) counts rows where student_id is non-null. With a non-null key like student_id they are equal, but on a nullable column they differ — a real source of off-by-some bugs in HAVING conditions.
• HAVING without GROUP BY. The whole table is treated as one group, so SELECT COUNT(*) FROM Students HAVING COUNT(*) > 100; returns the count only if the table has more than 100 rows. Rare, but legal.

The one-line mental model

WHERE decides which rows get into the buckets; HAVING decides which buckets make the cut. Once you internalize that WHERE runs before grouping and HAVING runs after, every rule above — which clause a condition belongs in, why aggregates are illegal in WHERE, why filtering early is cheaper — is just a consequence, not a fact to memorize.

Source

Adapted and corrected from the Knowledge Guide lesson HAVING (Databases › SQL Fundamentals), site/databases/sql-fundamentals/011-having.html. Behavior of HAVING, WHERE, and aggregate functions, and the engine-specific error messages and alias/portability notes, follow the SQL standard and the documented behavior of MySQL and PostgreSQL.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — HAVING

Why this concept exists (judgment chain)

WHERE filters rows before GROUP BY; HAVING filters groups after aggregates exist. Putting aggregates in WHERE fails; putting per-row predicates only in HAVING wastes work. Phase order is the entire mental model.

Worked example with numbers or traced steps

Students: Ann Math 91, Ben Math 85, Cara Math 62, Dan Eng 88, Eve Eng 80, Fay Hist 95, Gus Hist 54
WHERE score > 79 → 5 rows; GROUP BY course → Math:2 Eng:2 Hist:1
HAVING COUNT(*) > 1 → Math, Eng only (Hist dropped).
Without WHERE: Hist counts Fay+Gus=2 and wrongly passes HAVING.
Portable: HAVING COUNT(student_id) > 1 (not alias total_students).

When NOT to use / named alternative

Do not put score > 79 in HAVING when it is per-row — filter in WHERE first. Do not use HAVING without GROUP BY unless you intentionally treat the whole table as one group. Prefer WHERE for partition pruning/index use on base columns.

Failure / ops fingerprint

Fingerprint: ERROR 1111 Invalid use of group function; History appears in “high scorers” report because low scorers counted; MySQL accepts HAVING alias, Postgres rejects. Ops: code review phase-order; add EXPLAIN; standardize on repeated aggregate expressions for portability.

Hostile-panel drills (defend the decision)

Q1. Can WHERE use COUNT(*)?
Model answer: No — aggregates do not exist until after GROUP BY. Use HAVING.

Q2. Why filter score in WHERE not HAVING?
Model answer: Drops rows before grouping so COUNT reflects only high scorers; cheaper and correct for “count of good scores.”

Q3. HAVING on SELECT alias — portable?
Model answer: No. Logical order is HAVING before SELECT; MySQL may allow aliases; standard/PG require the aggregate expression.