Using DISTINCT with Aggregate Functions

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.63    

(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:    

------------    

15.97    

(1 row(s) affected)    

See Also