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
This excludes 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(*).
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';Indexes
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 COUNT(id) (where 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.
Reference
More articles: