Lessons · SQL · counting rows

How many rows

COUNT(*) counts rows. Without GROUP BY it always returns exactly one row, even when the answer is 0.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

How many orders today, how many customers in Leeds, did the import load anything: the first question asked of any table.

How to think about it

COUNT(*) for rows, COUNT(col) for rows where col is not NULL, COUNT(DISTINCT col) for different values. Add WHERE to count a subset; the answer is still one row.

Worked example

SELECT COUNT(*) FROM orders;

One row, one number: how many orders.

SELECT COUNT(*) FROM orders WHERE status = 'paid';

Still one row: the paid ones.

SELECT COUNT(*) FROM orders WHERE 1 = 0;

One row containing 0, not an empty result.

Your turn

How many customers have an email on file.

SELECT COUNT() FROM customers;

The trap

COUNT(col) skips NULLs and COUNT(*) does not. A column with gaps gives two different counts, and both are correct answers to different questions.

Practise counting rows on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.