The HAVING clause in SQL is used if we need to filter the result set based on aggregate functions such as MIN() and MAX(), SUM() and AVG() and COUNT().
SELECT COUNT(customer_id), country
FROM Customers
GROUP BY country
HAVING COUNT(customer_id) > 1;
Here, the SQL command:
- counts the number of rows by grouping them by country
- returns the result set if their count is greater than 1.
Note: The HAVING clause was introduced because the WHERE clause does not support aggregate functions. Also, GROUP BY must be used before the HAVING clause. To learn more, visit SQL GROUP BY.
SQL HAVING Vs WHERE
| HAVING Clause | WHERE Clause |
|---|---|
The HAVING clause checks the condition on a group of rows. |
The WHERE clause checks the condition on each individual row. |
The HAVING is used with aggregate functions. |
The WHERE clause cannot be used with aggregate functions. |
The HAVING clause is executed after the GROUP BY clause. |
The WHERE clause is executed before the GROUP BY clause. |
Let's take a look at an example,
If we want to select rows where the value of the amount column in the Orders table is less than 500, we can write,
SELECT customer_id, amount
FROM Orders
WHERE amount < 500;
Now, if we want to select rows and calculate sum off each amount, we can write,
SELECT customer_id, SUM(amount) AS total
FROM Orders
GROUP BY customer_id;
Thats it.
But if we need to select rows if the sum of amounts is less than 500 for any customer, we need to write,
SELECT customer_id, SUM(amount) AS total
FROM Orders
GROUP BY customer_id
HAVING SUM(amount) < 500;