# 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 `NULL`s, 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

## 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. `NULL`s 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.

More articles: