Articles Cookbook
Back to list
Standard SQL

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.

idfirst_namelast_namecity
1JohnWilliamsChicago
2TomBrownAustin
3LucyMillerChicago
4EllieSmithDallas
5BrianJonesAustin
6AllanDavisNULL

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.

Recommended courses:

See also:

go to top