The HAVING clause in SQL is used to filter groups of data after an aggregation operation. Unlike the WHERE clause, which filters rows before grouping, the HAVING clause applies conditions to aggregated data. In aggregation operations like SUM(), MAX() and etc, the WHERE clause does not worked. That's why we use HAVING to perform the same function as WHERE in MS SQL.
-
Syntax:
SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING condition;
Example
| Product | Amount |
|---|---|
| Pen | 200 |
| Notebook | 150 |
| Marker | 350 |
| Pen | 300 |
| Notebook | 200 |
| Marker | 100 |
SELECT Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Product
HAVING SUM(Amount) > 500;Output:
| Product | TotalSales |
|---|---|
| Pen | 500 |
| Marker | 450 |