Aggregate function calls cannot be nested on PostgreSQL

Question

I have a table:

name  | doc  | filter
NAME1 | DOC1 | A1
NAME1 | DOC1 | B1
NAME1 | DOC1 | C1
NAME2 | DOC2 | A1
NAME2 | DOC2 | D1
NAME2 | DOC2 | C1
NAME3 | DOC3 | B1
NAME3 | DOC3 | A1

So I create a SELECT command:

SELECT name, doc, array_agg(filter) filter from table group by name, doc, this return:

name  | doc  | filter
NAME1 | DOC1 | {A1,B1,C1}
NAME2 | DOC2 | {A1,D1,C1}
NAME3 | DOC3 | {B1,A1}

I am trying to create a SELECT command in prior table with the code below:

sum(case when array_agg(filter)::char like '%C1%' then 1 else 0 end) as "TOTAL"

But I receive the error:

aggregate function calls cannot be nested

How to solve it? If I want to add one more filter like this:

sum(case 
    when array_agg(filter)::char like '%C1%' or array_agg(filter)::char like '%C2%' 
    then 1 else 0 
end) as "TOTAL"`

How to do?

array_agg(filter)::char like '%C1%' does not make any sense to me (and if you want to cast an array you should cast it to ::text). What are you trying to do there? Count the number of values that are C1 in the filter column? Please edit your question (by clicking on the edit link below it) and add some sample data and the expected output based on that data as formatted text. See here for some tips on how to create nice looking text tables. — user330315
– user330315, Commented Mar 18, 2020 at 12:44
Are you maybe looking for count(*) filter (where "filter" = 'C1') as total? — user330315
– user330315, Commented Mar 18, 2020 at 12:45
@a_horse_with_no_name filter is an array of many values that I need to found — Italo Rodrigo
– Italo Rodrigo, Commented Mar 18, 2020 at 12:49
@a_horse_with_no_name filter is a field that was created by other table — Italo Rodrigo
– Italo Rodrigo, Commented Mar 18, 2020 at 12:55

GMB · Accepted Answer · 2020-03-18 12:46:16Z

1

I suspect that you just want a conditional count:

count(*) filter(where "filter" = 'C1') as total

This gives you the number of rows in the group where filter has value 'C1'.

If you want to take in account more filter values, then:

count(*) filter(where "filter" in ('C1', 'C2')) as total

Note that filter is a reserved word in Postgres, hence not a good choice for a column name.

answered Mar 18, 2020 at 12:46

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Aggregate function calls cannot be nested on PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related