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.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net