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?
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.
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.
\d tblin psql) and your version of Postgres. Add an explanation of what you are trying to achieve.