Here are three ways to count rows:

1. COUNT(*): count all rows

This counts every row, regardless of the values in the row. Often used with a GROUP BY to get common values, like in this “most popular names” query:

SELECT first_name, COUNT(*)
FROM people
GROUP BY first_name
ORDER BY COUNT(*) DESC
LIMIT 50

2. COUNT(DISTINCT column): get the number of distinct values

Really useful when a column has duplicate values. For example, this query finds out how many species every plant genus has:

SELECT genus, COUNT (DISTINCT species)
FROM plants
GROUP BY 1
ORDER BY 2 
ORDER BY DESC

“GROUP BY 1” means group by the first expression in the SELECT"

3. SUM(CASE WHEN expression THEN 1 ELSE 0 END) This trick using SUM and CASE lets you count how cats vs dogs vs other animals each owner has:

SELECT owner
, SUM(CASE WHEN type = 'dog' then 1 else 0 end) AS num_dogs
, SUM(CASE WHEN type = 'cat' then 1 else 0 end) AS num_cats
, SUM(CASE WHEN type NOT IN ('dog', 'cat') then 1 else 0 end)
end) AS num_other
FROM pets GROUP BY owner

pets: