Nested aggregate and subquery in postgresql/SQL with grouping

Question

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.

Answer 1

You might want to look into window functions:

http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.0#New_frame_options_for_window_functions