How to count distinct values in SQL

Problem

How can you count the distinct values in a SQL table?

Sample Data

Let's assume we have a table called Orders with the following data:

In this case, we want to count the distinct CustomerID values.

MySQL Solution

SELECT COUNT(DISTINCT CustomerID) AS DistinctCustomers
FROM Orders;

PostgreSQL Solution

SELECT COUNT(DISTINCT CustomerID) AS DistinctCustomers
FROM Orders;

Explanation

Both MySQL and PostgreSQL use the same syntax for counting distinct values.

The DISTINCT keyword in SQL is used to return only distinct (different) values. In this case, it's used within the COUNT() function to count the distinct CustomerID values from the Orders table.

The AS keyword is used to rename the column in the result set. Without it, the result would just be labelled as COUNT(DISTINCT CustomerID). By using AS DistinctCustomers, we make the result set more readable by renaming the column to DistinctCustomers.

The result of the SQL statement will be the number of distinct customers. In the sample data, the number of distinct CustomerID values is 3, so the result would be: