SQL HAVING

In this tutorial, we'll learn about the HAVING clause in SQL and how to use them with examples.

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.
How to use HAVING clause in SQL
Example: HAVING in SQL

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
Did you find this article helpful?