'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