Learn about SQL HAVING Clause with the help of examples. Learn how to use the SQL HAVING clause to filter grouped rows in your queries based on aggregate functions.
The SQL HAVING
clause 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 customers with the same first name based on their age count
SELECT COUNT(age) AS Count, first_name
FROM Customers
GROUP BY first_name
HAVING COUNT(age) > 1;
Here, the SQL command
age
of each row and groups them by first_name
age
is greater than 1 (thus filtering out customers with the same first_name
)The syntax of the SQL HAVING
clause is:
SELECT AggFunc(column), extra_columns
FROM table
GROUP BY target_column
HAVING condition
Here,
AggFunc(column)
refers to any aggregate function applied to a columnextra_columns
are other extra columns to filterGROUP BY
groups the data by target_column
HAVING condition
compares the column
to certain conditions that require filtering-- select the count of customer ids greater than one and their corresponding country
SELECT COUNT(customer_id), country
FROM Customers
GROUP BY country
HAVING COUNT(customer_id) > 1;
Here, the SQL command:
Example: HAVING in SQL
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.
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. |
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,
We can write a WHERE
clause to filter out rows where the value of amount in the Orders table is less than 500:
SELECT customer_id, amount
FROM Orders
WHERE amount < 500;
But with the HAVING
clause, we can use an aggregate function like SUM
to calculate the sum of amounts in the order table and get the total order value of less than 500 for each customer:
SELECT customer_id, SUM(amount) AS total
FROM Orders
GROUP BY customer_id
HAVING SUM(amount) < 500;
#sql