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.
Note that the result data type for a SUM operation on a DECIMAL(m,n) column is DECIMAL(p,n), as described in this table:
IF DBS Control field MaxDecimal is set to … | AND … | THEN p is … |
---|---|---|
|
m ≤ 15 | 15 |
15 < m ≤ 18 | 18 | |
m > 18 | 38 | |
18 | m ≤ 18 | 18 |
m > 18 | 38 | |
31 | m ≤ 31 | 31 |
m > 31 | 38 | |
38 | m = any value | 38 |