PostgreSQL pivot: aggregate function calls may not be nested error

Question

I am trying to pivot a table in postgreSQL using CASE, as suggested in this answer: https://stackoverflow.com/a/3008742/3063339. An example table:

CREATE TABLE scrap_pivot (event int, user_id text);
INSERT INTO scrap_pivot (event, user_id) VALUES
 (1, 'A')
,(2, 'A')
,(1, 'B')
,(3, 'B')
,(1, 'C')
,(2, 'D')
,(3, 'D');

However, the query below:

SELECT user_id,
SUM(CASE event WHEN 1 THEN count(user_id) ELSE 0 END) AS one,
SUM(CASE event WHEN 2 THEN count(user_id) ELSE 0 END) AS two,
SUM(CASE event WHEN 3 THEN count(user_id) ELSE 0 END) AS three,
FROM scrap_pivot
GROUP BY user_id;

returns the error:

ERROR: aggregate function calls may not be nested

Any insights as per why, and any possible workarounds? Of course I can split the query into two sub-queries, one for CASE and another for SUM, but this undermines performance considerably for large tables. Thanks!

You are making `COUNT(user_id)` and then `GROUP BY user_id`!? The result will always be 1. — Kouber Saparev, Nov 12 '14 at 12:49

score 1 · Accepted Answer · answered Nov 12 '14 at 12:37

I think this is what you want:

SELECT user_id,
       SUM(CASE event WHEN 1 THEN 1 ELSE 0 END) AS one,
       SUM(CASE event WHEN 2 THEN 1 ELSE 0 END) AS two,
       SUM(CASE event WHEN 3 THEN 1 ELSE 0 END) AS three,
FROM scrap_pivot
GROUP BY user_id;

As the error message suggests, you cannot nest aggregation functions. Instead, you just want conditional aggregation.

PostgreSQL pivot: aggregate function calls may not be nested error

1 Answers1