The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
You cannot use WHERE to filter the results of an aggregate function. Instead, you must use HAVING.
HAVING SyntaxSELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
Below is a selection from the Customers table:
Customers Table:
| CustomerID | CustomerName | Country |
|---|---|---|
| 1 | Alfreds Futterkiste | Germany |
| 2 | Ana Trujillo Emparedados | Mexico |
| 3 | Antonio Moreno Taquería | Mexico |
| 4 | Around the Horn | UK |
| 5 | Berglunds snabbköp | Sweden |
| 6 | Blauer See Delikatessen | Germany |
HAVING ExampleThe following SQL statement lists the number of customers in each country. It only includes countries with more than 1 customer.
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 1;
The result set would look like this:
| COUNT(CustomerID) | Country |
|---|---|
| 2 | Germany |
| 2 | Mexico |
WHERE vs. HAVINGWHERE clause is used to filter rows before any grouping or aggregation is performed. It operates on individual row data.HAVING clause is used to filter groups after the aggregation has been performed. It operates on the results of aggregate functions.You can use both in the same query. The WHERE clause is applied first, then the GROUP BY, and finally the HAVING clause.
-- List the number of customers in each country, but only for -- customers with CustomerID < 5, and only include countries -- with more than 1 customer. SELECT COUNT(CustomerID), Country FROM Customers WHERE CustomerID < 5 GROUP BY Country HAVING COUNT(CustomerID) > 1;