The HAVING Clause
We can now perform all sorts of aggregate operations using GROUP BY
. Sometimes, though, certain rows in aggregate functions may not be useful, and you may want to remove them from the query output. For example, when doing the customer counts, perhaps you are only interested in places that have at least 1,000
customers. Your first instinct may be to write something such as this:
SELECT Â Â state, COUNT(*) FROM Â Â customers WHERE Â Â COUNT(*)>=1000 GROUP BY Â Â state ORDER BY Â Â state;
However, you will find that the query does not work and gives you the following error:
In order to use the filter on aggregate functions, you need to use a new clause: HAVING
. The HAVING
clause is similar to the WHERE
clause, except it is specifically designed for GROUP BY
queries. The general structure of a GROUP BY
operation with a HAVING
statement is as...