In SQL, the
HAVING clause is used in conjunction with the
GROUP BY clause to filter the results of a query based on a condition applied to aggregated data. It is typically used when you want to filter the results based on the results of aggregate functions such as
Here’s the basic syntax of a query using
SELECT column1, column2, aggregate_function(column3) FROM table GROUP BY column1, column2 HAVING condition;
Let’s break down the components:
SELECT: Specifies the columns you want to include in the result set.
aggregate_function(column3): An aggregate function applied to column3, such as
FROM: Specifies the table or tables from which to retrieve data.
GROUP BY: Groups the result set by one or more columns (column1, column2 in the example).
HAVING: Specifies the condition that must be met for a row to be included in the result set. This condition can include the results of aggregate functions.
Here’s an example to illustrate the usage of
Suppose you have a table called
orders with columns
total_amount. You want to find customers who have made at least three orders and have spent a total amount greater than $100.
SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as total_spent FROM orders GROUP BY customer_id HAVING order_count >= 3 AND total_spent > 100;
In this example, the
HAVING clause filters the result set to include only those rows where the order count (
order_count) is greater than or equal to 3 and the total spent (
total_spent) is greater than 100.