The HAVING clause in SQL is used in combination with the GROUP BY clause to specify a condition for the groups of rows in a result set. The HAVING clause is used to filter the groups of rows based on the values in one or more columns, whereas the WHERE clause is used to filter the individual rows in a result set.
Syntax of SQL HAVING Clause
SELECT column1, aggregate_function(column2) FROM table_name WHERE condition GROUP BY column1 HAVING aggregate_function(column2) operator value;
In the above syntax, the “column1” is used to group the rows in the result set, and the “aggregate_function(column2)” is used to produce summarized results for each group of rows. The “WHERE” clause is used to filter the individual rows based on a specified condition, and it is optional. The “HAVING” clause is used to filter the groups of rows based on the aggregate function results, and it is only used in combination with the “GROUP BY” clause.
Example of SQL HAVING Clause
Consider the following table, “orders”:
| CustomerID | ProductID | Quantity |
|---|---|---|
| C001 | P001 | 2 |
| C002 | P002 | 3 |
| C001 | P002 | 1 |
| C002 | P001 | 4 |
Now, if we want to find the total quantity of each product ordered by each customer, and only show the customers who have ordered a total quantity of more than 2 products, we can use the following SQL statement with the HAVING clause:
SELECT CustomerID, ProductID, SUM(Quantity) as TotalQuantity FROM orders GROUP BY CustomerID, ProductID HAVING SUM(Quantity) > 2;
The output of this SQL statement would be:
| CustomerID | ProductID | TotalQuantity |
|---|---|---|
| C001 | P001 | 2 |
| C002 | P001 | 4 |
| C002 | P002 | 3 |
In this example, we grouped the rows in the “orders” table by “CustomerID” and “ProductID”, and used the SUM function to find the total quantity of each product ordered by each customer. The HAVING clause then filtered the groups of rows based on the condition “SUM(Quantity) > 2”, only showing the customers who have ordered a total quantity of more than 2 products.
Important Points to Remember about SQL HAVING Clause
- The HAVING clause is only used in combination with the GROUP BY clause, and it is used to filter the groups of rows based on the aggregate function results.
- The HAVING clause can be used with any aggregate function, such as SUM, AVG, COUNT, MAX, MIN, etc.
- The HAVING clause can be used to filter the groups of rows based on multiple conditions, by using the AND and OR operators.
- The HAVING clause is processed after the GROUP BY clause, and before the SELECT clause.
In conclusion, the SQL HAVING clause is a powerful tool that can be used to filter the groups of rows in a result set based on the aggregate function results. It can be used to find summarized results for a specific condition, and it is an essential part of any SQL query that involves grouping data.
