For an instructor lead, in-depth look at learning SQL click below.
When working with SQL, it’s important to use the correct clauses to filter and manipulate our data as required. Among the numerous clauses in SQL, the HAVING clause plays a critical role especially when you’re working with aggregate functions or when you want to filter results from a GROUP BY. But before we proceed any further, what really is the HAVING clause in SQL?
What is the HAVING Clause?
In SQL, the HAVING clause enables you to specify conditions that filter for groups of rows, rather than individual rows. Essentially, it can be considered as a WHERE clause for your GROUP BY. It is typically used with the GROUP BY clause to filter the results of aggregated fields.
How Does HAVING Work?
The HAVING clause works only on aggregated data – that is, data that is grouped using the GROUP BY clause and manipulated with an aggregate function like SUM, AVG, COUNT, MIN, or MAX.
Unlike the WHERE clause, which filters rows before they are grouped, the HAVING clause filters after rows have been grouped. This is why it is often compared to a filter for the GROUP BY clause.
Basic Syntax of HAVING in SQL:
|
1 2 3 4 5 6 7 8 |
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); |
Example of HAVING Clause with SQL Code
For instance, consider we have a table called ‘Orders’, with columns ‘orderId’, ‘customerId’, ‘orderAmount’. We want to find all the customers who have made total purchases of more than $5000.
|
1 2 3 4 5 6 |
SELECT customerId, SUM(orderAmount) as TotalOrderAmount FROM Orders GROUP BY customerId HAVING SUM(orderAmount) > 5000; |
In the above SQL query, we first GROUP BY ‘customerId’, summing up the ‘orderAmount’ for each ‘customerId’. Then, we filter out those customers who’s total ‘orderAmount’ is greater than 5000 using the HAVING clause.
Conclusion
To sum up, the HAVING clause in SQL provides an added layer of control when working with aggregated data, allowing you to filter data after it has been grouped. When used with the GROUP BY clause and aggregate functions, it becomes an efficient tool for filtering out specific groups.