How to Filter Records with Aggregate Function COUNT

Problem

You want to find groups of rows with a specific number of entries in a group.

Example

Our database has a table named product with data in the following columns: id, name and category.

Let’s find the category of products with more than two entries.

Solution

SELECT category, COUNT(id)
FROM product
GROUP BY category
HAVING COUNT(id) > 2;

Here are the results:

Discussion

To filter records according the given number of rows in the group, use the HAVING clause. It filters rows using in a condition aggregate function like COUNT.

First, in SELECT, use the name of a column or columns to group rows (this is category in our example), then place the aggregate function COUNT, which tallies the number of records in each group. To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id)).

Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After GROUP BY use the HAVING clause to filter records with aggregate functions like COUNT. HAVING is always used after the GROUP BY clause. In HAVING, we use a condition to compare a value with one returned by the aggregate function. In the example, we compare whether COUNT(id) returns a value higher than two. If true, the category is returned with the product count.