Можно ли запустить LISTAGG для уже сгруппированной строки?

Oracle 11g R2 через SqlDeveloper:

Как сгруппировать строку с помощью функции LISTAGG, если эта строка уже была сгруппирована? Эта ошибка возвращается: 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

В результатах должно быть указано количество первичных местоположений для каждого EIN со списком специальностей поставщика — без дубликатов.

Я могу подобраться очень близко, но в столбце SPEC возвращаются дубликаты, поскольку некоторые поставщики имеют одну и ту же специальность в разных местах. У некоторых провайдеров есть 15-20 местоположений, поэтому наличие дубликатов в этом столбце может привести к путанице.

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

Большинство решений для этого использовали SELECT DISTINCT или что-то подобное, но я не могу исключить какие-либо строки, потому что мне нужно, чтобы каждая строка была возвращена, чтобы ее можно было включить в подсчет основных местоположений.

Я попытался сначала сгруппировать все вместе, а затем использовать LISTAGG в операторе SELECT, но я получаю сообщение об ошибке, и я думаю, что это связано с группировкой строки, которая уже была сгруппирована.

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;

sql oracle-sqldeveloper oracle11gr2 listagg ora-00937
person Kyle    schedule 28.06.2015    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вы хотите, чтобы внешний запрос объединял значения spec, поэтому просто добавьте GROUP BY, в котором упоминаются все остальные выходные данные. Также я думаю, что вы, вероятно, хотите, чтобы внутренние COUNT были SUMmed.

Попробуйте это (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;
person Community    schedule 28.06.2015
comment
Это имеет слишком большой смысл. Вот почему я не подумал об этом. Большое спасибо! - person Kyle; 29.06.2015