Databases & SQL · Class 12 · Aggregate Functions & Grouping

MySQLaggregates⏱️ 8 min read

HAVING

“Which cities have more than two teachers?” You can already produce the count for every city. What you cannot yet do is throw away the cities that do not qualify — because by the time the counts exist, where has long since finished.

1Why where cannot do this

The order of events in a grouped query is fixed, and it explains everything about this lesson:

where

Throws away individual rows.

group by

Piles up the survivors.

having

Throws away whole groups.

order by

Sorts what is left.

where runs at step 1, when there are no groups and no counts — only individual teachers. Asking it about count(*) is asking about something that does not exist yet. having runs at step 3, once every group has its total.

2Filtering the groups

Cities with more than two teachers:

MySQL command line client

mysql> select count(*), city from teachers group by city having count(*) > 2;

+----------+---------+

| count(*) | city |

+----------+---------+

| 3 | Gangtok |

| 3 | Namchi |

+----------+---------+

2 rows in set (0.00 sec)

The previous lesson produced five cities. Three of them — Singtam, Siliguri and Geyzing, with two teachers each — have been dropped because their group failed the test. Two rows remain.

The same idea on the students table:

MySQL command line client

mysql> select count(*), grade from students group by grade having count(*) >= 3;

+----------+-------+

| count(*) | grade |

+----------+-------+

| 3 | 10 |

| 4 | 12 |

+----------+-------+

2 rows in set (0.00 sec)

3having can test any aggregate

Not only counts. Cities whose students average above 80 — note that the condition tests avg(percent) while the column shown is rounded:

MySQL command line client

mysql> select round(avg(percent),2), city from students group by city having avg(percent) > 80;

+-----------------------+---------+

| round(avg(percent),2) | city |

+-----------------------+---------+

| 84.5 | Singtam |

| 87.38 | Gangtok |

| 95.2 | Namchi |

+-----------------------+---------+

3 rows in set (0.00 sec)

Three cities out of six clear the bar. The others, including the group of students whose city is NULL, are gone.

4When no group qualifies

Raise the bar high enough and every group fails. The query is still correct; there is simply nothing to report:

MySQL command line client

mysql> select count(*), city from teachers group by city having count(*) > 5;

Empty set (0.00 sec)

No city has six teachers, so all five groups were discarded. Empty set is the honest answer to a question whose answer is “none”.

5Using where and having together

They are not alternatives — a query can use both, and each does its own job:

select count(*), city
  from students
 where grade = 12          ← keep only class 12 students
 group by city
having count(*) > 1;       ← keep only cities with more than one of them

Read it as a sentence: of the class 12 students, grouped by city, show the cities with more than one. The where narrows the people; the having narrows the cities.

where vs having

	where	having
Filters	Individual rows	Whole groups
Runs	Before grouping	After grouping
Can it use an aggregate?	No — the totals do not exist yet	Yes — that is the point
Needs group by?	No	Almost always used with one
Position in the query	After from	After group by

The test that decides which to use

Does your condition mention count(), sum(), avg(), max() or min()? If yes, it must be having. If it only mentions ordinary column values, it belongs in where — and putting it there is faster, because those rows are discarded before any grouping work is done.