Aggregates on Distinct Values

Note

MySQL 5 or Later The use of DISTINCT in aggregate functions, as about to be described, was added in MySQL 5.0.3. The following does not work in MySQL 4.x.

The five aggregate functions can all be used in two ways:

• To perform calculations on all rows, specify the ALL argument, or specify no argument at all (because ALL is the default behavior).

• To only include unique values, specify the DISTINCT argument.

Tip

ALL Is Default The ALL argument need not be specified because it is the default behavior. If DISTINCT is not specified, ALL is assumed.

The following example uses the AVG() function to return the average product price offered by a specific vendor. It is the same SELECT statement used in the previous example, but here the DISTINCT argument is used so the average only takes into account unique prices:

• Input

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

• Output

+-----------+ | avg_price | +-----------+ | 15.998000 | +-----------+

• Analysis

As you can see, in this example avg_price is higher when DISTINCT is used because there are multiple items with the same lower price. Excluding them raises the average price.

Caution

Caution DISTINCT may only be used with COUNT() if a column name is specified. DISTINCT may not be used with COUNT(*), and so COUNT(DISTINCT *) is not allowed and generates an error. Similarly, DISTINCT must be used with a column name and not with a calculation or expression.

Tip

Using DISTINCT with MIN() and MAX() Although DISTINCT can technically be used with MIN() and MAX(), there is actually no value in doing so. The minimum and maximum values in a column are the same whether or not only distinct values are included.