SQL Having Clause

When would one use the HAVING keyword in SQL?

It seems to me that the HAVING keyword in SQL is a common question asked in technical interviews. No one uses it though if you're writing code, chances are you'll pull all the data out and get what you need through the code. Looking at the HAVING keyword you may realize it can be quite useful to let SQL do all the heavy lifting and skip the WHERE.

First, lets establish a basic table for our example...

ID	TYPE	QUANTITY	DESCRIPTION
1	Animal	8	Ocelot
2	Vegetable	4	Cardoon
3	Animal	4	Banteng
4	Animal	1	Guenon
5	Vegetable	8	Fluted Pumpkin

So there we have a table. First lets get the quantities of everything. SELECT type, SUM(quantity) as total from table GROUP BY type We'd end up with:

type	total
Animal	13
Vegetable	12

What can HAVING do for us? It can give a way to search the GROUP BY data easily. So, lets just look for quantities greater than 12. SELECT type, SUM(quantity) as total from table GROUP BY type HAVING SUM(quantity) > 12

type	total
Animal	13

How is HAVING different from WHERE?

The HAVING keyword is followed by some aggregate function SUM, AVG, MIN, MAX. You cannot do the aggregate functions with the WHERE clause and this is a primary difference. When you're doing a WHERE, you're looking at a row by row comparison versus HAVING which is working with grouped data.