HAVING

After GROUP BY you have one row per tier. Say you only want the tiers whose customers average more than 10 visits. The instinct is WHERE AVG(visits) > 10, but this is not how filtering on groups is done in SQL.

name                 visits  favorite_station  tier
Mrs. Papadaki        42      grill             vip
The Nikolaou family  15      salad             regular
Petros               3       pastry            new
Widow Fotiou         60      grill             vip
Ilias                8       salad             regular
A pair of tourists   1       pastry            new

The reason: WHERE is checked before the rows are grouped, back when they are still individual customers. There is no average of a tier yet, so there is nothing for that condition to test.

HAVING is checked after the grouping and the aggregate. By then every tier has its number, and HAVING keeps or drops whole tiers based on it. It goes right after the GROUP BY.

SELECT tier, AVG(visits)
FROM customers
GROUP BY tier
HAVING AVG(visits) > 10;

tier     avg
vip      51
regular  11.5

Here is the difference in one picture. WHERE throws out customers; HAVING throws out tiers. This WHERE drops the most-visited regular first, then averages whatever is left in each tier, so every tier still shows up but its average has moved.

SELECT tier, AVG(visits)
FROM customers
WHERE visits < 50
GROUP BY tier;

tier     avg
vip      42
regular  11.5
new      2

Same table, same numbers to start from. The HAVING query above uses every customer and then removes a tier. The WHERE query removes one customer and keeps every tier. Different tool, different result.

Still, a query can use both: WHERE narrows the rows going in, HAVING narrows the groups coming out. The example below shows how it works on Konstantinos' wine list.

name          type     region     price
Assyrtiko     white    Santorini  14
Agiorgitiko   red      Nemea      12
Xinomavro     red      Naoussa    16
Moschofilero  rosé     Mantinia   10
Malagousia    white    Macedonia  11
Mavrodafni    dessert  Patras     9

SELECT type, AVG(price)
FROM wines
WHERE price > 10
GROUP BY type
HAVING AVG(price) > 13;

type  avg
red   14

SQL runs the clauses in this order: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. WHERE near the start, before there are any groups. HAVING near the end, once the groups and their numbers exist.

Rule of thumb: if the condition is about one row on its own, it is a WHERE. If it is about a whole group, a COUNT, a SUM, an AVG, it is a HAVING.