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

1. 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“

1. 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: