agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Using aggregates to get sums and partial sums in one query
Date: Sat, 8 Nov 2014 02:18:55 +0100 (CET)
Message-ID: <VisenaEmail.42.9d35d8da90dccb9.1498cc15e1f@tc7-visena> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
view thread (4+ messages) latest in thread
Message-ID: <VisenaEmail.42.9d35d8da90dccb9.1498cc15e1f@tc7-visena>
Permalink: ../VisenaEmail.42.9d35d8da90dccb9.1498cc15e1f@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.42.9d35d8da90dccb9.1498cc15e1f@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: Using aggregates to get sums and partial sums in one query
In-Reply-To: <VisenaEmail.42.9d35d8da90dccb9.1498cc15e1f@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox