Filtering GROUP BY with HAVING and WHERE in SQL
By FoxLearn 3/7/2025 8:15:31 AM 82
- Use
WHERE
to filter individual rows before the grouping. - 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.
CustomerID | Item | PurchaseDate |
---|---|---|
101 | Laptop | 2023-06-15 |
102 | Phone | 2023-06-15 |
101 | Tablet | 2023-06-15 |
103 | Headphones | 2023-06-16 |
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 aPurchaseDate
of'2023-06-15'
.GROUP BY
groups the filtered rows byCustomerID
.- The query then counts how many items each customer bought on that date.
Query Result:
CustomerID | ItemsPurchased |
---|---|
101 | 2 |
102 | 1 |
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 byCustomerID
.HAVING
filters the groups where the count of items purchased is greater than 1.
Query Result:
CustomerID | ItemsPurchased |
---|---|
101 | 2 |
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.
- How to find all the dependencies of a table in SQL Server
- How to Find Objects Referencing a Table in SQL Server
- Case sensitivity in SQL Server
- How to Convert varchar to uniqueidentifier in SQL Server
- How to use GROUP BY in SQL
- Using GROUP BY with ORDER BY in SQL
- Aggregate functions with GROUP BY in SQL
- How to Copy data from one table to another in SQL