I need to do the same group by on a bunch of different aggregates that I'm getting with nested subqueries in Postgresql 8.3.
If I do this:
select f10 as report_id,
(SELECT AVG(age)
FROM (select f10 as report_id,
f62 as age
from reports
where f55 in ('1'))
and f62 in ('1', '2', '3', '4', '5'))) foo
group by report_id) as agg1,
(SELECT AVG(age)
FROM (select f10 as report_id,
f62 as age
from reports
where f55 in ('2'))
and f62 in ('1', '2', '3', '4', '5'))) foo
group by report_id) as agg2,
from reports
group by report_id;
it is almost what I want but the group by doesn't do anything- all the aggregates are the same, it is an aggregate across all report_ids. I want a separate aggregate per report_id.
If I try to do the grouping within the aggregates then I can't return more than 2 fields or rows and so it doesn't work.
It has been suggested to me to do
sum(case
when f55 in ('1') then f62
else 0
end) / sum(case
when f55 in ('1') then 1
else 0
end)
...etc. for each of the aggregates but I don't think this is a good way to go. Just can't seem to figure out anything better.
