CODEWITHSUNDEEP
sqlarrayspostgresqlgroup-bycount
Italo Rodrigo
Italo Rodrigo

Reputation: 1785

Aggregate function calls cannot be nested on PostgreSQL

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?

Upvotes: 0

Views: 649

Answers (1)

GMB
GMB

Reputation: 222682

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.

Upvotes: 1

Related Questions