How to Count Distinct Values in SQL

Database:

Operators:

DISTINCT, COUNT

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.

id	first_name	last_name	city
1	John	Williams	Chicago
2	Tom	Brown	Austin
3	Lucy	Miller	Chicago
4	Ellie	Smith	Dallas
5	Brian	Jones	Austin
6	Allan	Davis	NULL

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:

cities
3

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.