How to Use GROUP BY and HAVING Clause in SQL

When working with SQL, you’ll often need to group data to analyze and summarize it. The GROUP BY statement makes this possible by allowing you to group rows based on column values. But what if you need to filter grouped data? That’s where the HAVING clause comes in.

In this blog post, I’ll explain how to use GROUP BY and HAVING clause in SQL, provide examples, and highlight the difference between the HAVING clause and the WHERE clause. By the end, you’ll understand how to filter grouped data effectively!

Understanding the HAVING Clause

The HAVING clause in SQL is used to filter grouped data after it’s been processed by the GROUP BY clause. This makes it different from the WHERE clause, which filters rows before grouping.

For example:

• Use WHERE to filter individual rows before grouping.
• Use HAVING to filter groups created by the GROUP BY statement.

Syntax of GROUP BY and HAVING

Here’s the general syntax for using GROUP BY and HAVING together:

SELECT column_name, aggregate_function(column_name)  
FROM table_name  
WHERE condition  
GROUP BY column_name  
HAVING condition

1. SELECT: Specifies the columns and aggregate functions you want to retrieve.
2. WHERE: Filters rows before grouping.
3. GROUP BY: Groups the filtered rows by one or more columns.
4. HAVING: Filters the grouped data.

How To Use GROUP BY and HAVING Clause in SQL?

Let’s say you have the Order Details table with columns Product ID and Quantity. If you want to calculate the total quantity for each product/item, and include only the ones where total quantity exceeds 1000 pieces, you can write the following code:

SELECT ProductID, SUM(Quantity) AS TotalQty
FROM [Order Details]
GROUP BY ProductID
HAVING SUM(Quantity) > 1000

What’s happening here?

• The GROUP BY groups rows by ProductID.
• The SUM function calculates total quantity for each product.
• The HAVING clause filters out products where total quantity sold is over 1000.

Here is what it looks like in SQL Server Management Studio:

Another Group By and Having Clause Example with WHERE

You can combine WHERE and HAVING in the same query to filter both individual rows and grouped data. For example, if you want to include only products that have price below $20 and then calculate the total quantity sold per each product, filtering products with total sales above 1000 pieces, you can write this code:

SELECT ProductID, SUM(Quantity) AS TotalQty
FROM [Order Details]
WHERE UnitPrice < 20
GROUP BY ProductID
HAVING SUM(Quantity) > 1000

In this case:

• WHERE UnitPrice < 20 filters rows where individual item price is below $20.
• GROUP BY ProductID groups the remaining rows by Product.
• HAVING SUM(Quantity) > 1000 filters the products based on the total quantity sold.

Here is the result in SSMS:

HAVING Clause vs. WHERE Clause

It’s important to understand the key differences between HAVING and WHERE:

HAVING:

1. Filters grouped data (after GROUP BY).
2. Can use aggregate functions (e.g., SUM, AVG).

WHERE:

1. Filters rows (before GROUP BY).
2. Cannot use aggregate functions.

WHERE Clause Example:

SELECT ProductID, Quantity
FROM [Order Details]
WHERE UnitPrice < 20

This query filters rows where UnitPrice of the Product is below $20, and it does this before grouping.

HAVING Clause Example

SELECT ProductID, SUM(Quantity) AS TotalQty
FROM [Order Details]
GROUP BY ProductID
HAVING SUM(Quantity) > 1000

This query filters products where the total quantity of sold products (sum of Quantity) exceeds 1000 pieces after grouping.

When to Use GROUP BY and HAVING Clause in SQL

The HAVING clause is particularly useful in scenarios where you need to:

• Filter grouped data, such as products, sales or customers with aggregate totals.
• Perform advanced data analysis by applying conditions to calculated values.

Group By and Having Clause Example: Multiple Columns

You can also use the HAVING clause with multiple columns. For example, if you want to calculate total freight for each order by CustomerID and EmployeeID, and filter results where total freight exceeds 1000 kilograms or pounds:

SELECT CustomerID, EmployeeID, SUM(Freight) AS TotalFreight
FROM [Orders]
GROUP BY CustomerID, EmployeeID
HAVING SUM(Freight) > 1000

This groups rows by both CustomerID and EmployeeID, calculates the sum of Freight for each group, and filters out groups with totals above 1000.

Here are the results in SSMS:

Conclusion

Understanding how to use GROUP BY and HAVING clause in SQL is essential for working with grouped data. The GROUP BY statement lets you summarize data, while the HAVING clause gives you the ability to filter results based on aggregate calculations.

With the examples provided, you now have the tools to confidently use GROUP BY and HAVING in your SQL queries. Whether you’re analyzing sales data or building complex reports, mastering these clauses will make your work much easier.

Happy coding!

PS. Make sure you check one of my previous blog posts on How to Use GROUP BY and WHERE Condition in SQL.

If you would like to learn more about SQL, check out this amazing book on Amazon.