Filtering GROUP BY with HAVING and WHERE in SQL

1. Use WHERE to filter individual rows before the grouping.
2. Use HAVING to filter the results after the grouping has been done.

To put it simply, WHERE filters the rows before grouping, GROUP BY groups the filtered rows, and HAVING filters the groups.

Using WHERE with GROUP BY

Imagine you have a table recording purchases made by customers, and you want to know how many items each customer bought on a specific date.

To get the number of items each customer purchased on 2023-06-15, you would use WHERE to filter the rows and then group the results by customer:

SELECT CustomerID, COUNT(*) as ItemsPurchased
FROM Purchases
WHERE PurchaseDate = '2023-06-15'
GROUP BY CustomerID

In this example:

• WHERE filters the table, including only the rows with a PurchaseDate of '2023-06-15'.
• GROUP BY groups the filtered rows by CustomerID.
• The query then counts how many items each customer bought on that date.

Query Result:

Using HAVING with GROUP BY

Now, let’s say you want to group the purchases by CustomerID, but you only want to include customers who bought more than one item. To do this, you would use HAVING after grouping:

SELECT CustomerID, COUNT(*) as ItemsPurchased
FROM Purchases
GROUP BY CustomerID
HAVING COUNT(*) > 1

In this example:

• GROUP BY groups all rows by CustomerID.
• HAVING filters the groups where the count of items purchased is greater than 1.

Query Result:

In summary:

• WHERE filters rows before grouping, and it’s used to narrow down the dataset for the grouping operation.
• HAVING filters after the rows are grouped, and it's used to refine which groups to include based on aggregate conditions.

You can combine WHERE and HAVING in a query when needed, using WHERE for row filtering and HAVING for group filtering. Both can be used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX to specify the filtering conditions.