Counts rows or values that aren't NULL.
COUNT(*)
COUNT(<column>)
COUNT(DISTINCT <column>)
COUNT(*) counts all rows. COUNT(<column>) — only those where the column isn't NULL. COUNT(DISTINCT <column>) — how many different values there are. Together with GROUP BY it counts separately for each group.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| total |
|---|
| 5 |
| all_rows | with_bonus | departments |
|---|---|---|
| 5 | 3 | 3 |
Two employees have no bonus, so COUNT(bonus) gives 3, not 5.
| department | people |
|---|---|
| backend | 2 |
| design | 1 |
| frontend | 2 |
COUNT(<column>) silently skips NULL. If you want the number of rows, write COUNT(*).
If there are no rows, COUNT gives 0, while SUM gives NULL. So there's no need to wrap COUNT in COALESCE.
With a LEFT JOIN, COUNT(*) counts 1 even where no match was found. To get 0, count a column from the right-hand table: COUNT(o.id).