DataslopeFiltering Groups with HAVING
Why WHERE cannot filter on a SUM or COUNT, and how HAVING lets you keep only the groups that meet a condition.
You can group rows and summarize each group. Now: how do you keep
only the groups whose summary meets a condition — "departments
with more than 2 people," "products that sold over $1000"? You
cannot use WHERE for this, and the reason why reveals something
deep about how queries run.
Why WHERE can't do it
WHERE runs before grouping. At that moment, the groups — and
therefore their COUNTs and SUMs — do not exist yet. Asking
WHERE COUNT(*) > 2 is like asking about a total before anything
has been added up.
HAVING is the answer: it filters groups, after the
aggregates have been computed. Think of it as "WHERE, but for
groups."
HAVING in action
Keep only departments with more than two employees:
Only Engineering (3 people) survives. Sales (2) and Marketing (1)
are filtered out as groups. Notice HAVING uses an aggregate —
COUNT(*) — which is exactly what WHERE could not do.
WHERE and HAVING together
They are not rivals — they work as a team, each at its own stage:
WHEREfilters rows before grouping ("only count full-time staff").HAVINGfilters groups after aggregating ("only show departments whose average pay exceeds 80k").
Read the three commented steps top to bottom — they are the actual order the database works in. The intern row is removed first, then groups form, then the average-pay filter runs on those groups.
A quick rule of thumb
If your filter mentions an aggregate (COUNT, SUM, AVG,
MIN, MAX), it belongs in HAVING. If it mentions only plain
column values, it belongs in WHERE. Putting row filters in
WHERE is also more efficient, because fewer rows reach the
grouping step.
The full clause order so far
You have now met the core query clauses. Here is their fixed order — and, helpfully, roughly the order the database executes them:
We will revisit this execution order in detail in the Composing
Queries section — for now, knowing where HAVING fits is the key
takeaway.
Check your understanding
Why can't you write WHERE COUNT(*) > 2 to keep groups with more than two rows?
Because COUNT(*) is not a real function.
Because WHERE runs before grouping, so the group counts don't exist yet when it is evaluated.
Because WHERE can only be used without GROUP BY.
Because counts can never be compared with >.
What does HAVING filter?
Individual rows before they are grouped.
Groups, after their aggregates have been computed.
The columns shown in the output.
The order of the result rows.
You want to summarize only full-time staff, then keep only departments whose average salary exceeds 80000. Where does each condition go?
Both conditions go in WHERE.
Both conditions go in HAVING.
The full-time filter goes in WHERE; the average-salary condition goes in HAVING.
Neither can be expressed in SQL.
Grouping Data with GROUP BY
The leap from one grand total to one summary per category — how GROUP BY splits rows into buckets and aggregates each one separately.
Foreign Keys
The column that turns two separate tables into a connected pair — how a foreign key points at a primary key, and how it protects your data's integrity.