COUNT DISTINCT with CASE WHEN … THEN …

How to use COUNT DISTINCT function?

Syntax

COUNT(DISTINCT expr,[expr...])

COUNT DISTINCT function returns the number of unique non-NULL values after removing all the duplicated row using the given expression.

Example

We have table company

emp_no	title	from_date	to_date
10001	Senior Engineer	1986-06-26	9999-01-01
10002	Staff	1996-08-03	9999-01-01
10003	Senior Engineer	1995-12-03	9999-01-01
10004	Engineer	1986-12-01	1995-12-01
10004	Senior Engineer	1995-12-01	9999-01-01
10005	Senior Staff	1996-09-12	9999-01-01

Now count the number of titles using COUNT DISTINCT function

SELECT
  COUNT(DISTINCT title) AS total_titles
FROM
  company;

The result is:

total_titles
4

Another example, we have this dep_manager table storing data of all department managers of the company:

emp_id	dept_id	from_date	to_date
110022	d001	1985-01-01	1991-10-01
110039	d001	1991-10-01	9999-01-01
110085	d002	1985-01-01	1989-12-17
110114	d002	1989-12-17	9999-01-01
110183	d003	1985-01-01	1992-03-21
110228	d003	1992-03-21	9999-01-01
110303	d004	1985-01-01	1988-09-09
110344	d004	1988-09-09	1992-08-02
110386	d004	1992-08-02	1996-08-30
110420	d004	1996-08-30	9999-01-01
110511	d005	1985-01-01	1992-04-25
110567	d005	1992-04-25	9999-01-01
110725	d006	1985-01-01	1989-05-06
110765	d006	1989-05-06	1991-09-12
110800	d006	1991-09-12	1994-06-28
110854	d006	1994-06-28	9999-01-01
111035	d007	1985-01-01	1991-03-07
111133	d007	1991-03-07	9999-01-01
111400	d008	1985-01-01	1991-04-08
111534	d008	1991-04-08	9999-01-01
111692	d009	1985-01-01	1988-10-17
111784	d009	1988-10-17	1992-09-08
111877	d009	1992-09-08	1996-01-03
111939	d009	1996-01-03	9999-01-01

Now count the number of departments:

SELECT
  COUNT(DISTINCT dept_id) AS total_dept
FROM
  dept_manager;

The result is:

total_dept
9

How to use COUNT DISTINCT with CASE WHEN … THEN …?

We have this sales table recording the name of item sold and name of the customer who bought it.

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

First, count the total number of orders in the table:

SELECT
  COUNT(*) AS total_orders
FROM
  sales;

The result:

total_orders
12

A customer might place multiple orders, now count the number of unique customers:

SELECT
  COUNT(DISTINCT customer_name) AS unique_customer
FROM
  sales;

The results:

unique_customer
3

A customer might purchase an item multiple times, so to count the number of unique item purchased by each customer:

SELECT
  COUNT(DISTINCT CASE WHEN customer_name = 'Alex' THEN item END) AS by_Alex,
  COUNT(DISTINCT CASE WHEN customer_name = 'Bob' THEN item END) AS by_Bob,
  COUNT(DISTINCT CASE WHEN customer_name = 'Jim' THEN item END) AS by_Jim
FROM
	sales;

The results is

by_Alex	by_Bob	by_Jim
2	3	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.