The DISTINCT keyword is optional with SUM, AVG, and COUNT. It is not allowed with MIN, MAX, or COUNT(*). When DISTINCT is used, duplicate values are eliminated before the sum, average, or count is calculated.
If you use DISTINCT, the expression must consist of a column name only. It cannot include an arithmetic expression.
DISTINCT is used inside parentheses and before the column name. The following statement returns the average of the distinct prices of business books:
SELECT AVG(DISTINCT price) |
|||
FROM titles |
|||
WHERE type = 'business' |
|||
------------ |
|||
14.63 |
|||
(1 row(s) affected) |
|||
Without DISTINCT, the AVG function finds the average price of all business titles in the titles table:
SELECT AVG(price) |
|||
FROM titles |
|||
WHERE type = 'business' |
|||
------------ |
|||
15.97 |
|||
(1 row(s) affected) |
|||