Lessons · SQL · COUNT and NULL

COUNT(*) versus COUNT(column)

COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL.

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 customers' and 'how many customers with an email' are different numbers.

How to think about it

Rows, rows with a value, or distinct values? Ask which question you mean. Rows: COUNT(*). Rows with a value: COUNT(col). Distinct values: COUNT(DISTINCT col).

Worked example

SELECT COUNT(*), COUNT(email), COUNT(DISTINCT city)

Three different counts from one table.

FROM customers;

Same table for all three.

Your turn

How many distinct ratings exist?

SELECT COUNT( rating) FROM films;

The trap

COUNT(col) silently drops NULLs; if you meant rows, use *.

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