postgres aggregate function calls may not be nested

Question

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?

Answer 1

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.