postgres aggregate function calls may not be nested
postgresql, sql
Solution
If you need to nest aggregation functions, you will need to use some form of subquery. I am using `product` column as an arbitrary choice for grouping column. I also renamed `Count` to `dcount`.
SQLFiddle
Sample data:
create table sample (
product varchar,
dcount int,
impressions int,
volume int
);
insert into sample values ('a', 100, 10, 50);
insert into sample values ('a', 100, 20, 40);
insert into sample values ('b', 100, 30, 30);
insert into sample values ('b', 100, 40, 30);
insert into sample values ('c', 100, 50, 10);
insert into sample values ('c', 100, 60, 100);
Query:
select
sum(frequency) as frequency
from
(
select
product,
sum((impressions / dcount::numeric) * volume) / sum(volume) as frequency
from
sample
group by
product
) x;
The point is that you cannot nest aggregate functions. If you need to aggregate aggregates then you need to use subquery.
Problem
I have a query: ``` select sum( sum((Impressions / Count) * Volume) / sum(Volume) ) as frequency from datatable; ``` however I cannot execute this in postgres because it uses nested aggregations. Is there another way to write this without using nested aggregations?