Re: Using aggregates to get sums and partial sums in one query

Hi all.

I'm trying to retrieve some aggregated sums in one query.

This is a short example of what I'm trying to achieve.

The schema (not very good names, I know):

drop table if exists t_member_budget;

drop table if exists t_member;

drop table if exists t_project;

create table t_project(

    id integer primary key,

    name varchar not null

);

create table t_member(

    id integer primary key,

    person_id integer not null,

    project_id integer not null REFERENCES t_project(id),

    role varchar not null,

    years_experience integer not null

);

create table t_member_budget(

    id integer PRIMARY KEY,

    member_id integer not null REFERENCES t_member(id),

    month date not null,

    amount integer not null

);

insert into t_project(id, name) values(1, 'P1');

insert into t_project(id, name) values(2, 'P2');

-- Person 1,2,3 is member of project 1. Person 1 has 2 roles - so 2 entries 

insert into t_member(id, person_id, project_id, role, years_experience) 

  values(1, 1, 1, 'LEADER', 3); -- person 1 member of project 1 as LEADER

insert into t_member(id, person_id, project_id, role, years_experience)

  values(2, 1, 1, 'MEMBER', 7); -- person 1 member of project 1 as MEMBER

insert into t_member(id, person_id, project_id, role, years_experience) 

  values(3, 2, 1, 'LEADER', 1); -- person 2 member of project 1

insert into t_member(id, person_id, project_id, role, years_experience) 

  values(4, 3, 1, 'LEADER', 6); -- person 3 member of project 1

-- Person 3 is member of project 2 

insert into t_member(id, person_id, project_id, role, years_experience) 

  values(5, 3, 2, 'LEADER', 5); -- person 3 member of project 2

insert into t_member_budget(id, member_id, month, amount) values(1, 1, '2014-01-01'::date, 2); -- person 1 in project 1 as LEADER

insert into t_member_budget(id, member_id, month, amount) values(2, 1, '2014-02-01'::date, 3); -- person 1 in project 1 as LEADER

insert into t_member_budget(id, member_id, month, amount) values(3, 1, '2014-03-01'::date, 2); -- person 1 in project 1 as LEADER

insert into t_member_budget(id, member_id, month, amount) values(4, 2, '2014-01-01'::date, 2); -- person 1 in project 1 as MEMBER

insert into t_member_budget(id, member_id, month, amount) values(5, 3, '2014-01-01'::date, 2); -- person 2 in project 1

insert into t_member_budget(id, member_id, month, amount) values(6, 4, '2014-01-01'::date, 2); -- person 3 in project 1

insert into t_member_budget(id, member_id, month, amount) values(7, 5, '2014-01-01'::date, 4); -- person 3 in project 2

insert into t_member_budget(id, member_id, month, amount) values(8, 5, '2014-01-01'::date, 2); -- person 3 in project 2

Then what I'm trying to get out is this dataset:

select p.name, sum(years_experience) as years_exp_in_project, count(role) as num_roles

    , count(distinct m.person_id) as unique_members, sum(g.amount) as sum_budget_amount

from t_project p JOIN t_member m ON m.project_id = p.id

    join t_member_budget g ON g.member_id = m.id

group by p.id

order by p.name;

This gives me: