For an instructor lead, in-depth look at learning SQL click below.
Welcome back to our ongoing series on SQL programming. In this edition, we’re going to cover HAVING, a clause in SQL that allows you to filter aggregated data.
Let’s start by understanding why we need to use HAVING clause in SQL.
Why use HAVING
The WHERE clause in SQL is a powerful tool for filtering rows of data based on condition(s). But when combined with aggregate functions like COUNT(), SUM(), AVG(), it can’t be used for filtering the aggregated data. This is where HAVING comes into play.
When to use HAVING
The general rule of thumb is to use HAVING when you want to filter results after an aggregation has taken place.
Basic Syntax
|
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); |
Examples
Example 1: Using COUNT with HAVING
Imagine you have a table ‘orders’ and you want to find out only those ‘product categories’ from it, which have more than 5 entries.
|
1 2 3 4 5 6 |
SELECT product_category, COUNT(product_id) FROM orders GROUP BY product_category HAVING COUNT(product_id) > 5; |
Example 2: Using SUM with HAVING
Now imagine that you want to filter out only rows with total sale being more than a certain value.
|
1 2 3 4 5 6 |
SELECT salesperson, SUM(sale_amount) FROM sales GROUP BY salesperson HAVING SUM(sale_amount) > 50000; |
Conclusion
That’s all for the basics of SQL HAVING clause. The HAVING clause is a very powerful SQL operator and can greatly simplify our work when it comes to filtering aggregated data. Just remember that it works hand-in-hand with GROUP BY for filtering groups based on specific conditions.