Re: JSONB_AGG: aggregate function calls cannot be nested

Alexander Farber schrieb am 20.02.2021 um 19:39:
> So I am trying:
>
> # SELECT
>                 JSONB_AGG(TO_CHAR(finished, 'YYYY-MM-DD')) AS day,
>                 JSONB_AGG(SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int) AS completed,
>                 JSONB_AGG(SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int) AS expired
>         FROM words_games
>         WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
>         GROUP BY day
>         ORDER BY day;
> ERROR:  aggregate function calls cannot be nested
> LINE 3:                 JSONB_AGG(SUM(CASE WHEN reason='regular' or ...

You need a second level of grouping:

select day as day,
jsonb_agg(completed) as completed,
jsonb_agg(expired) as expired)
from (
SELECT TO_CHAR(finished, 'YYYY-MM-DD') AS day,
SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int AS completed,
SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int) AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
GROUP BY day
ORDER BY day;

Btw:

SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int AS completed,

can also be written as

count(*) filter (where reason in ('regular', 'resigned') as completed