How to Count Distinct Values in SQL

Problem

You’d like to count how many different non-NULL values there are in a given column.

Example

Our database has a table named customer with data in the following columns: id, first_name, last_name, and city.

Let’s find the number of different (and non-NULL) cities.

Solution

SELECT COUNT(DISTINCT city) as cities
FROM customer;

This query returns number of cities where customers live:

Discussion

To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT. When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.