Aggregates on Distinct Values
The five aggregate functions can all be used in two ways:
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:
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+ | avg_price | +-----------+ | 15.998000 | +-----------+
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.
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.