SQL HAVING

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:

1. counts the number of rows by grouping them by country
2. 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.

HAVING Clause With JOIN

The HAVING clause can also be used with SQL JOIN. For example,

SELECT C.customer_id, C.first_name, SUM(O.amount)
FROM Customers AS C
JOIN Orders AS O
ON C.customer_id = O.customer_id
GROUP BY C.customer_id
HAVING SUM(O.amount) >= 500;

Here, the SQL command,

1. joins the two table based on customer_id
2. groups the rows based on customer_id
3. calculates the SUM of amount for each customer
4. returns the result-set whose total amount is greater than or equal to 500