It is possible to run LISTAGG on a row already grouped?

Question

Oracle 11g R2 via SqlDeveloper:

How do I group a row using LISTAGG function if that row has already been grouped? This error is returned: ORA-00937: not a single-group group function

CREATE TABLE doc
(name varchar (10), id int, ein int, prim varchar (1), spec varchar (20));
INSERT INTO doc (name, id, ein, prim, spec)
values ('SARAH', 77, 1234, 'Y', 'CARDIOLOGY');
INSERT INTO doc (name, id, ein, prim, spec)
values ('SARAH', 77, 1234, 'Y', 'CARDIOLOGY');
INSERT INTO doc (name, id, ein, prim, spec)
values ('JAMES', 45, 8686, 'Y', 'SURGERY');
INSERT INTO doc (name, id, ein, prim, spec)
values ('JAMES', 45, 9123, 'Y', 'FAMILY');
INSERT INTO doc (name, id, ein, prim, spec)
values ('LISA', 23, 7457, 'N', 'INTERNAL');
INSERT INTO doc (name, id, ein, prim, spec)
values ('LISA', 23, 7457, 'Y', 'SURGERY');
INSERT INTO doc (name, id, ein, prim, spec)
values ('BILL', 11, 6391, 'N', 'PEDIATRICS');
INSERT INTO doc (name, id, ein, prim, spec)
values ('BILL', 11, 6391, 'N', 'PEDIATRICS');
INSERT INTO doc (name, id, ein, prim, spec)
values ('BILL', 11, 6391, 'N', 'PEDIATRICS');
INSERT INTO doc (name, id, ein, prim, spec)
values ('BILL', 11, 6391, 'Y', 'PEDIATRICS');     

NAME     ID     EID     PRIMARY LOCATION     SPEC
BILL     11     6391         N              PEDIATRICS
BILL     11     6391         N              PEDIATRICS
BILL     11     6391         N              PEDIATRICS
BILL     11     6391         Y              PEDIATRICS
JAMES    45     8686         Y               SURGERY
JAMES    45     9123         Y               FAMILY
LISA     23     7457         N               SURGERY
LISA     23     7457         Y               INTERNAL
SARAH    77     1234         Y               CARDIOLOGY
SARAH    77     1234         Y               CARDIOLOGY

The results need to be a count of primary locations for each EIN, with a list of the provider's specialties - without duplicates.

I can get really close, but I have duplicates being returned in the SPEC column, since some of the providers have the same specialty at multiple locations. Some providers have 15-20 locations, so having duplicates in this column can get really messy.

SELECT
  name, id, ein,
  SUM(CASE WHEN prim = 'Y' THEN 1 ELSE 0 END) count,
  LISTAGG(spec, ',') WITHIN GROUP (ORDER BY spec) spec
FROM doc
GROUP BY NAME, ID, EIN
ORDER BY NAME;


    Results:
NAME    ID    EIN    COUNT   SPEC
BILL    11    6391    1      PEDIATRICS,PEDIATRICS,PEDIATRICS,PEDIATRICS
JAMES   45    8686    1      Surgery
JAMES   45    9123    1      FAMILY
LISA    23    7457    1      INTERNAL,SURGERY
SARAH   77    1234    2      CARDIOLOGY,CARDIOLOGY 

Most of the solutions to this have used SELECT DISTINCT or something similar, but I can't exclude any rows because I need every row to be returned so it can be included in the count of primary locations.

I tried first grouping everything together, then using LISTAGG on the SELECT statement, but I get the error message, and I think it's due to grouping a row that's already been grouped.

SELECT
  Name, ID, EIN, COUNT,
  LISTAGG(SPEC, ',') WITHIN GROUP (ORDER BY spec) spec
FROM
(
SELECT 
  NAME, ID, EIN, 
  SUM(CASE WHEN PRIM = 'Y' THEN 1 ELSE 0 END) COUNT, 
  SPEC
FROM DOC
GROUP BY name, id, ein, spec
)
order by name;

Answer 1

You want the outer query to combine spec values, so just add a GROUP BY that mentions all the other outputs. Also I think you probably want the inner COUNTs to be SUMmed.

Try this (sqlfiddle)

SELECT
  Name, ID, EIN, sum(COUNT),
  LISTAGG(SPEC, ',') WITHIN GROUP (ORDER BY spec) spec
FROM
(
SELECT 
  NAME, ID, EIN, 
  SUM(CASE WHEN PRIM = 'Y' THEN 1 ELSE 0 END) COUNT, 
  SPEC
FROM DOC
GROUP BY name, id, ein, spec
)
GROUP BY name, id, ein
order by name;