Day 23 – SQL HAVING Clause
The SQL HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a query based on the result of an aggregate function.
The syntax for the HAVING clause is as follows:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING aggregate_function(condition)
In this syntax,
column_name(s) refers to the column(s) that you want to retrieve data from,
table_name refers to the table that you want to retrieve data from,
condition is an optional parameter that can be used to filter the results, and
aggregate_function is a function that operates on the grouped data and returns a single result.
When you use the HAVING clause, the result set is first grouped based on the columns specified in the GROUP BY clause. Then, the aggregate function specified in the HAVING clause is applied to the grouped data. If the result of the aggregate function satisfies the condition specified in the HAVING clause, then the group is included in the result set.
For example, suppose you have a table called
orders with columns
order_amount. You could use the following query to group the orders by customer and retrieve the total amount of orders for each customer that is greater than 1000:
SELECT customer_name, SUM(order_amount) FROM orders GROUP BY customer_name HAVING SUM(order_amount) > 1000
This query would return a result set that shows the total order amount for each customer in the
orders table, but only for those customers whose total order amount is greater than 1000.
Note that the HAVING clause is used to filter the results of a query after the data has been grouped, whereas the WHERE clause is used to filter the results of a query before the data has been grouped.