How to Count Distinct Values in SQL
Database:
Operators:
Table of Contents
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.