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:

Figure 3.13: Error showing the query not working

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...