How to count a table rows, a table distinct rows, a column values, missing values, etc.
Count a table rows
Example: How many users do we have?
SELECT count(*) FROM users;
Count a column values
NULLs, so only actual values are counted.
Example: How many users gave us their home address?
SELECT count(address) FROM user;
When doing this, you may want to exclude empty texts, or zeroes, or other values that mean “nothing”. To exclude empty texts:
SELECT count(address) FROM user WHERE address <> '';
Count missing column values
If we consider NULLs as missing values, we can count them to find out how many values are missing.
Example: How many users did not tell us their home address?
SELECT count(*) FROM user WHERE address IS NULL;
Count distinct column values
Values that appear N times are counted N times.
NULLs are not counted.
Example: How many different addresses do we have, if we consider that some users share the same address?
SELECT count(DISTINCT address) FROM user;
For columns with a
UNIQUE constraint, this is the same as
Count a occurrences of a specific value
It’s possible to count how many times a value occurs in a column.
Examples: How many users are called John? How many of them are called John Smith?
SELECT count(*) FROM user WHERE name = 'John'; SELECT count(*) FROM user WHERE name = 'John' AND surname = 'Smith';
To speed up all of these queries, make sure you have an index whose first column is the one you need to count.
There is a common misconception that
id is the primary key) is faster than
COUNT(*). This is not true, because the optimizers are smart enough to use the fastest way to count a table rows, under the hood.