Re: JSONB_AGG: aggregate function calls cannot be nested

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Sat, Feb 20, 2021 at 11:46 AM Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

Then I have to split the query in 3 similar ones (with same condition)?

I try:

SELECT

                JSONB_AGG(TO_CHAR(finished, 'YYYY-MM-DD')) AS day

        FROM words_games

        WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'

        GROUP BY day

        ORDER BY day;

ERROR:  aggregate functions are not allowed in GROUP BY

LINE 2:                 JSONB_AGG(TO_CHAR(finished, 'YYYY-MM-DD')) A...

                        ^

That's a whole different misunderstanding of aggregates that you are seeing.

I mis-spoke in the prior response though.  Its not that you only get one column of an aggregate function per select - you only get to use a single aggregate in each _expression_ in a select/group-by.  array_agg(sum(...)) is two aggregates in a single _expression_.

David J.