Aggregates on Distinct Values
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 above, but here the DISTINCT argument is used so that the average only takes into account unique prices:
SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'dll01';
avg_price ----------- 4.2400
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
DISTINCT may only be used with COUNT() if a column name is specified. DISTINCT may not be used with COUNT(*). 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 actually be used with MIN() and MAX(), there is actually no value in doing so. The minimum and maximum values in a column will be the same whether or not only distinct values are included.
Tip
Additional Aggregate Arguments In addition to the DISTINCT and ALL arguments shown here, some DBMSs support additional arguments such as TOP and TOP PERCENT that let you perform calculations on subsets of query results. Refer to your DBMS documentation to determine exactly what arguments are available to you.