SQL – Filtering GROUP BY with HAVING and WHERE

by

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):

Table showing all rows and highlighting individual rows to include in groups

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 UserName
Code 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:

SQL query results showing two rows

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):

Query results showing all groups (before filtering the highlighted groups)

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(*) > 2
Code 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:

SQL query results of filtering grouped results

You can use one or more of the SQL aggregate functions (MIN/MAX/AVG/SUM/COUNT) with HAVING to specify the logical condition(s) for the groups you want to keep.

Related Articles

Comments are closed.