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
