Example: Simple Uses of DISTINCT
The following statement returns the number of unique job titles.
SELECT COUNT(DISTINCT JobTitle) FROM …
The following statement lists the unique department numbers in the employee table.
SELECT DISTINCT dept_no FROM employee;
The result returned is:
dept_no ------- 100 300 500 600 700
Example: Using DISTINCT with Aggregates
The following statement uses the DISTINCT operator to eliminate duplicate values before applying SUM and AVG aggregate operators, respectively, to column x:
SELECT SUM(DISTINCT x), AVG(DISTINCT x) FROM test_table;
You can only perform DISTINCT aggregations at the first, or highest level of aggregation if you specify subqueries.
The result data type for a SUM operation on a DECIMAL(m,n) column is DECIMAL(p,n).