Count a table rows

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: