Chapter 3: Using Aggregates, Grouping, and Sorting

Aggregate functions with distinct

You can use the optional keyword distinct only with sum, avg, count_big, and count. When you use distinct, Adaptive Server eliminates duplicate values before performing calculations.

If you use distinct, you cannot include an arithmetic expression in the argument. The argument must use a column name only. distinct appears inside the parentheses and before the column name. For example, to find the number of different cities in which there are authors, enter:

select count(distinct city) 
from authors

------------- 
           16
 
(1 row affected)

For an accurate calculation of the average price of all business books, omit distinct. The following statement returns the average price of all business books:

select avg(price) 
from titles 
where type = "business"

------------- 
        13.73 
 
(1 row affected)

However, if two or more books have the same price and you use distinct, the shared price is included only once in the calculation:

select avg(distinct price) 
from titles 
where type = "business"

------------- 
        11.64 
 
(1 row affected)

View this book as PDF