Filtering by Group Count

In SQL, it is possible to group results and filter based on the number of rows within each group. This can be achieved using the HAVING clause.

Consider the following requirement:

Problem Statement:

Is it possible to group results and then filter by how many rows are in the group? For example:

SELECT * FROM mytable WHERE COUNT(*) > 1 GROUP BY name

Solution:

The HAVING clause allows us to apply a filter on an aggregate function. In this case, we can filter on the COUNT(*) aggregate function to select groups with more than one row. The correct syntax is:

SELECT name, COUNT(*)
FROM mytable
GROUP BY name
HAVING COUNT(*) > 1

This query will return all the unique names and the count of rows associated with each name, where the count is greater than 1.