How to Count the Number of Rows with the Same Value

Published Sep 20, 2021

Suppose we have a table in our SQL database.

dogval
corgi5
pug5
shih tzu10
schnauzer20

We want the number of occurrences of all values in a particular column.

valcount
52
101
201
SELECT val, COUNT(*) AS count
FROM table_name
GROUP BY dog;

The notable section here is the GROUP BY clause. It subdivides the dataset into chunks based on unique combinations of the specified fields. We can specify multiple fields if we want unique combinations of those fields.

If we want to filter by some specific count number, we can use the HAVING operator. HAVING is for group-level criteria while WHERE is for row-level criteria.

Let’s get only the dogs that have a count >= 10.

SELECT val, COUNT(*) AS count
FROM table_name
GROUP BY dog
HAVING count >= 10;

More SQL Articles