Including or excluding duplicates in the result set

You can use the ALL, DISTINCT, or UNIQUE keywords to qualify the scope of an aggregate function.

If you include an aggregate scope qualifier, it must be the first item in the argument list.

The ALL keyword specifies that all values selected from the column or expression, including any duplicate values, are used in the calculation. Because ALL is the default scope for aggregate functions, the following two aggregate expressions are equivalent, and their return value is based on the value of all qualifying rows in the ship_weight column:

AVG(ship_weight)
AVG(ALL ship_weight)

Including the DISTINCT keyword as the first argument to the aggregate function restricts its subsequent arguments to unique values from the specified column. The UNIQUE and DISTINCT keywords are synonyms in this context. The following two aggregate expressions are equivalent, and their return value is based on the set of unique values in qualifying rows of the ship_weight column:

AVG(DISTINCT ship_weight)
AVG(UNIQUE ship_weight)

If several qualifying rows have the same ship_weight value, only one instance of that value is included in calculating the value of the aggregate.

If a query includes the DISTINCT or UNIQUE keyword (rather than the ALL keyword or no keyword) in the Projection clause whose Select list also includes an aggregate function whose argument list begins with the DISTINCT or UNIQUE keyword, the database server issues an error, as in the following example: .

SELECT DISTINCT AVG(DISTINCT ship_weight)
       FROM orders;

That is, it is not valid in the same query for both the Projection clause and for an aggregate function to restrict the result set to unique values.

If the Projection clause does not specify the DISTINCT or UNIQUE keyword of the SELECT statement, however, the query can include one or more aggregate functions that each includes the DISTINCT or UNIQUE keyword as the first specification in the argument list, as in the following example:

SELECT AVG(UNIQUE ship_weight), COUNT (DISTINCT customer_num)
       FROM orders;