Articles Cookbook
Back to list
Standard SQL

How to Filter Records with Aggregate Function COUNT

Database:

Operators:

COUNT, HAVING

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.

idnamecategory
1sofafurniture
2glovesclothing
3T-Shirtclothing
4chairfurniture
5deskfurniture
6watchelectronics
7armchairfurniture
8skirtclothing
9radio receiverelectronics

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:

categorycount
furniture4
clothing3

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 a 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 using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause. It’s 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.

Recommended courses:

Recommended articles:

See also:

go to top