The SQL HAVING Clause
The SQL HAVING clause and the WHERE clause are similar. Both are used to filter table rows according to predetermined criteria.
The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a query based on the aggregated values of grouped rows in SQL. It allows you to specify conditions for aggregated values, such as SUM, AVG, COUNT, MAX, or MIN.
Let’s have some sql HAVING clause with examples.
Examples of SQL HAVING clause
Example 1: HAVING with GROUP BY and COUNT
Suppose you have a table called Orders with columns CustomerID and TotalAmount, and you want to find customers who have placed more than five orders.
You can use the GROUP BY and HAVING clauses as below:
SQL> SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5;
GROUP BY CustomerID groups the rows based on the CustomerID column.
COUNT(OrderID) calculates the total number of orders for each customer group.
HAVING COUNT(OrderID) > 5 filters the grouped rows, showing only those with more than three orders.
Example 2: HAVING with GROUP BY and AVG
Suppose you have a table called Sales with columns ProductID and UnitPrice and you want to find products with an average unit price greater than $50.
SQL> SELECT ProductID, AVG(UnitPrice) AS AveragePrice
FROM Sales
GROUP BY ProductID
HAVING AVG(UnitPrice) > 50;
GROUP BY ProductID groups the rows based on the ProductID column.
AVG(UnitPrice) calculates the average unit price for each product group.
HAVING AVG(UnitPrice) > 50 filters the grouped rows, showing only products with an average price greater than $50.
Example 3: Using HAVING with GROUP BY and MAX
Suppose you have a table called Employees with columns DepartmentID and Salary, and you want to find departments where the highest salary is greater
than $60,000. You can use the GROUP BY and HAVING clauses as follows:
SQL> SELECT DepartmentID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID
HAVING MAX(Salary) > 60000;
GROUP BY DepartmentID groups the rows based on the DepartmentID column.
MAX(Salary) calculates the highest salary for each department group.
HAVING MAX(Salary) > 60000 filters the grouped rows, showing only departments with a maximum salary greater than $60,000.
When you want to filter the outcomes of aggregate functions used on grouped data, the HAVING clause is helpful. When working with grouped and aggregated data, It is a valuable tool for drawing conclusions from your data because it enables you to specify conditions based on the summarized data.