How to Count the Number of Rows with the Same Value
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;