Using DISTINCT to Eliminate Duplicates

7.5.1 Problem

You want to know which values are present in a set of values, without listing duplicate values a bunch of times. Or you want to know how many distinct values there are.

7.5.2 Solution

Use DISTINCT to select unique values, or COUNT(DISTINCT) to count them.

7.5.3 Discussion

A summary operation that doesn't use aggregate functions is to determine which values or rows are contained in a dataset by eliminating duplicates. Do this with DISTINCT (or DISTINCTROW, which is synonymous). DISTINCT is useful for boiling down a query result, and often is combined with ORDER BY to place the values in more meaningful order. For example, if you want to know the names of the drivers listed in the driver_log table, use the following query:

mysql> SELECT DISTINCT name FROM driver_log ORDER BY name;
+-------+
| name |
+-------+
| Ben |
| Henry |
| Suzi |
+-------+

A query without DISTINCT produces the same names, but is not nearly as easy to understand:

mysql> SELECT name FROM driver_log;
+-------+
| name |
+-------+
| Ben |
| Suzi |
| Henry |
| Henry |
| Ben |
| Henry |
| Suzi |
| Henry |
| Ben |
| Henry |
+-------+

If you want to know how many different drivers there are, use COUNT(DISTINCT):

mysql> SELECT COUNT(DISTINCT name) FROM driver_log;
+----------------------+
| COUNT(DISTINCT name) |
+----------------------+
| 3 |
+----------------------+

COUNT(DISTINCT) ignores NULL values. If you also want to count NULL as one of the values in the set if it's present, do this:

COUNT(DISTINCT val) + IF(COUNT(IF(val IS NULL,1,NULL))=0,0,1)

The same effect can be achieved using either of the following expressions:

COUNT(DISTINCT val) + IF(SUM(ISNULL(val))=0,0,1)
COUNT(DISTINCT val) + (SUM(ISNULL(val))!=0)

COUNT(DISTINCT) is available as of MySQL 3.23.2. Prior to that, you have to use some kind of workaround based on counting the number of rows in a SELECT DISTINCT query. One way to do this is to select the distinct values into another table, then use COUNT(*) to count the number of rows in that table.

DISTINCT queries often are useful in conjunction with aggregate functions to obtain a more complete characterization of your data. For example, applying COUNT(*) to a customer table indicates how many customers you have, using DISTINCT on the state values in the table tells you which states you have customers in, and COUNT(DISTINCT) on the state values tells you how many states your customer base represents.

When used with multiple columns, DISTINCT shows the different combinations of values in the columns and COUNT(DISTINCT) counts the number of combinations. The following queries show the different sender/recipient pairs in the mail table, and how many such pairs there are:

mysql> SELECT DISTINCT srcuser, dstuser FROM mail
 -> ORDER BY srcuser, dstuser;
+---------+---------+
| srcuser | dstuser |
+---------+---------+
| barb | barb |
| barb | tricia |
| gene | barb |
| gene | gene |
| gene | tricia |
| phil | barb |
| phil | phil |
| phil | tricia |
| tricia | gene |
| tricia | phil |
+---------+---------+
mysql> SELECT COUNT(DISTINCT srcuser, dstuser) FROM mail;
+----------------------------------+
| COUNT(DISTINCT srcuser, dstuser) |
+----------------------------------+
| 10 |
+----------------------------------+

DISTINCT works with expressions, too, not just column values. To determine the number of hours of the day during which messages in the mail were sent, count the distinct HOUR( ) values:

mysql> SELECT COUNT(DISTINCT HOUR(t)) FROM mail;
+-------------------------+
| COUNT(DISTINCT HOUR(t)) |
+-------------------------+
| 12 |
+-------------------------+

To find out which hours those were, list them:

mysql> SELECT DISTINCT HOUR(t) FROM mail ORDER BY 1;
+---------+
| HOUR(t) |
+---------+
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 17 |
| 22 |
| 23 |
+---------+

Note that this query doesn't tell you how many messages were sent each hour. That's covered in Recipe 7.16.