SQL HAVING CLAUSE
The HAVING clause is a filtering tool in SQL that works in conjunction with the GROUP BY clause. While the WHERE clause filters rows before grouping, the HAVING clause is applied after the grouping occurs. This enables the filtering of groups based on aggregated data.
The HAVING clause is typically used to filter results based on conditions related to aggregate functions like SUM, AVG, COUNT, MAX, and MIN. For example, you might want to filter groups where the sum of sales is greater than a certain amount or where the average score of a student is above a threshold.
The main difference between the WHERE and HAVING clauses is that the WHERE clause filters data before any grouping occurs, while the HAVING clause is used to filter groups after the data has been grouped.
Some key points about the HAVING clause:
- The HAVING clause can be used only with the GROUP BY clause.
- It can filter results based on the results of aggregate functions.
- You cannot use HAVING without GROUP BY, unless it is used with aggregate functions over the entire result set.
Example
In this example, we will use the HAVING clause to filter products with a total price greater than 1000. This query demonstrates how you can apply conditions to the results of grouped data.
Code Example
-- Using HAVING to filter groups based on an aggregate function
SELECT Product, SUM(Price) AS TotalPrice
FROM Orders
GROUP BY Product
HAVING SUM(Price) > 1000;
Output
| Product | TotalPrice |
|---|---|
| Laptop | 1200.00 |
Explanation
- The query first groups the data by Product and calculates the total price for each product using the SUM function.
- The HAVING clause then filters these groups to only show those products where the total price is greater than 1000.
Without the HAVING clause, it would be impossible to filter results based on the aggregated values, like the total price or the average sales, which are only calculated after grouping.
In this example, only the Laptop product has a total price above 1000, so it is the only product shown in the output.
The HAVING clause is essential when working with complex data sets where you need to filter results based on aggregated values, such as total sales, average ratings, or total revenue.