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 existst_project; create table t_project( id integer primary key,
namevarchar 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
entriesinsert 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
intot_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 1insert 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 2insert 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
LEADERinsert 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
intot_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: name | years_exp_in_project
| num_roles | unique_members | sum_budget_amount
------+----------------------+-----------+----------------+-------------------
P1 | 17 | 4 | 3 | 13
P2 | 5 | 1 | 1 | 6
This query kind of illustrates what I'm after, but gives the wrong results,
of course: 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
joint_member_budget g ON g.member_id = m.id group by p.id order by p.name;
This gives me: name | years_exp_in_project | num_roles | unique_members |
sum_budget_amount
------+----------------------+-----------+----------------+-------------------
P1 | 23 | 6 | 3 | 13
P2 | 10 | 2 | 1 | 6
Which obviously is wrong. I know I can craft a query which uses
sub-queries and CTE to get the desired results, but I hope there is cleaner
solution. Any idea how to craft a neat query for this without sub-queries or
CTE? Will Windowing-functions help out here? Thanks. -- Andreas Joseph Krogh
CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>