Using DISTINCT
The DISTINCT keyword is optional with SUM, AVG, and 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.
This query returns the average prices of business books (without duplicate values):
USE pubs
SELECT AVG(DISTINCT price)
FROM titles
WHERE type = 'business'
Here is the result set:
------------------
14.64
(1 row(s) affected)
Without DISTINCT, the AVG function finds the average price of all business titles:
USE pubs
SELECT AVG(price)
FROM titles
WHERE type = 'business
Here is the result set:
------------------
13.73
(1 row(s) affected)