There are two ways to filter GROUP BY results:
- Use WHERE to filter individual rows before grouping.
- Use HAVING to filter grouped results.
In other words, WHERE controls which rows will be considered for grouping, GROUP BY groups the filtered rows, and then HAVING filters the groups. I’ll show examples below.
Example – Using WHERE with GROUP BY
Let’s say you have the following table and you want to see how many movies each user watched on a date (rows highlighted):
Here’s how to use WHERE with GROUP BY to produce the grouped results on the filtered rows:
SELECT UserName, COUNT(*) as MoviesWatched FROM Streaming WHERE StreamedDate = '2022-04-28' GROUP BY UserNameCode language: SQL (Structured Query Language) (sql)
WHERE filters the rows so that only a subset of rows are included in the grouping. GROUP BY groups the filtered rows. This query produces the following results:
Example – Using HAVING with GROUP BY
HAVING allows you to filter groups based on a condition about the group. For example, let’s say you have the following query results where you grouped by date, but you want to only show date groups having more than two rows (highlighted):
Here’s how to use HAVING with GROUP BY to filter the grouped results:
SELECT StreamedDate, COUNT(*) as StreamCount FROM Streaming GROUP BY StreamedDate HAVING COUNT(*) > 2Code language: SQL (Structured Query Language) (sql)
GROUP BY groups the rows by date, then HAVING filters the grouped results based on the condition. This produces the following results:
You can use one or more of the aggregate functions (MIN/MAX/AVG/SUM/COUNT) with HAVING to specify the logical condition(s) for the groups you want to keep.