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 … |
|
0 15 |
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 |