How to Count the Number of Rows with the Same Value

Published Sep 20, 2021  ∙  Updated May 2, 2022

Suppose we have a table in our SQL database.

dog val
corgi 5
pug 5
shih tzu 10
schnauzer 20

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

val count
5 2
10 1
20 1
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;