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.
HAVING clause was introduced because the
WHERE clause does not support aggregate functions.
HAVING Clause With JOIN
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,
- joins the two table based on customer_id
- groups the rows based on customer_id
- calculates the SUM of amount for each customer
- returns the result-set whose total amount is greater than or equal to 500