Back to list Standard SQL How to Filter Records with Aggregate Function COUNT Database: SQL PostgreSQL MS SQL Server Oracle MySQL SQLite 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: SQL Basics SQL Basics in SQL Server SQL Practice Set Recommended articles: An Introduction to Using SQL Aggregate Functions with JOINs A Beginner’s Guide to SQL Aggregate Functions See also: How to Find Maximum Values in Rows How to Find Minimum Values in Columns How to Find the Minimum Value of a Column in SQL How to Find the Maximum Value of a Numeric Column in SQL Tags: SQL PostgreSQL MS SQL Server Oracle MySQL SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.