How to count unique rows in MySQL with COUNT DISTINCT?

Overview

In MySQL, COUNT() function returns the number of rows in a table.

COUNT(DISTINCT expression) returns the number of rows that contain non NULL values as the result of the specified expression.

Syntax

To to use COUNT(DISTINCT) in a SELECT statement:

SELECT
	COUNT(DISTINCT expression)
FROM
	table_name;

Example

We have table sales:

item	customer_name
book	Alex
pen	Bob
book	Alex
book	Jim
shoes	Jim
pen	Bob
backpack	Bob
book	Bob
wallet	Alex
wallet	Alex
book	Bob
backpack	Jim

Since a customer might have multiple purchases, the customer_name field has duplicate values. So to count the number of unique customers:

SELECT
	COUNT(DISTINCT customer_name)
FROM
	sales;

The result is 3.

A customer might purchased one item multiple times, to see how many unique items each customer already purchased, we can use GROUP BY:

SELECT
	customer_name,
	COUNT(DISTINCT item) as unique_item
FROM
	sales
GROUP BY
	customer_name;

The result is:

customer_name	unique_item
Alex	2
Bob	3
Jim	3

We can also include WHERE to count the number of unique customers that bought the item book:

SELECT
	COUNT(DISTINCT customer_name) book_customers
FROM
	sales
WHERE
	item = 'book';

The result is 3.

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.