'Concatenate distinct strings and numbers
I am trying to get a distinct concatenated list of employee_ids and sum their employee_allowance. However, I do not want to sum duplicate employee_id's employee_allowance.
My expected result
| name | employee_ids | allowance | this column is for explanation (not part of output) |
|---|---|---|---|
| Bob | 11Bob532, 11Bob923 | 26 | 13+13=26 because the id's are different, so we sum both |
| Sara | 12Sara833 | 93 | |
| John | 18John243, 18John823 | 64 | 21+43=64 because we got rid of the duplicate 18John243's allowance |
Table creation/dummy data
CREATE TABLE emp (
name varchar2(100) NOT NULL,
employee_id varchar2(100) NOT NULL,
employee_allowance number not null
);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('Bob', '11Bob923', 13);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('Bob', '11Bob532', 13);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('Sara', '12Sara833', 93);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('John', '18John243', 21);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('John', '18John243', 21);
INSERT INTO emp (name, employee_id, employee_allowance) VALUES ('John', '18John823', 43);
My attempt
My output gives me the distinct, concatenated employee_ids but still sums up the duplicate employee_allowance row.
SELECT
name,
LISTAGG(DISTINCT employee_id, ', ') WITHIN GROUP (ORDER BY employee_id) "ids",
SUM(employee_allowance)
FROM emp
GROUP BY
name
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
