From: Bill MacArthur <webmaster@dhs-club.com>
To: pgsql-sql@postgresql.org
Subject: reduce many loosely related rows down to one
Date: Sat, 25 May 2013 03:19:38 -0400
Message-ID: <51A0660A.2010806@dhs-club.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Here is a boiled down example of a scenario which I am having a bit of difficulty solving.
This is a catchall table where all the rows are related to the "id" but are entered by different unrelated processes that do not necessarily have access to the other data bits.
CREATE TABLE test (
id INTEGER,
rspid INTEGER,
nspid INTEGER,
cid INTEGER,
iac BOOLEAN,
newp SMALLINT,
oldp SMALLINT,
ppv NUMERIC(7,2),
tppv NUMERIC(7,2)
);
INSERT INTO test (id, rspid, nspid, cid, iac) VALUES (1,2,3,4,TRUE);
INSERT INTO test (id, rspid, nspid, newp) VALUES (1,2,3,100);
INSERT INTO test (id, rspid, nspid, oldp) VALUES (1,2,3,200);
INSERT INTO test (id, rspid, nspid, tppv) VALUES (1,2,3,4100);
INSERT INTO test (id, rspid, nspid, tppv) VALUES (1,2,3,3100);
INSERT INTO test (id, rspid, nspid, ppv) VALUES (1,2,3,-100);
INSERT INTO test (id, rspid, nspid, ppv) VALUES (1,2,3,250);
INSERT INTO test (id, rspid, nspid, cid) VALUES (2,7,8,4);
-- raw data now looks like this:
select * from test;
id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv
----+-------+-------+-----+-----+------+------+---------+---------
1 | 2 | 3 | 4 | t | | | |
1 | 2 | 3 | | | 100 | | |
1 | 2 | 3 | | | | 200 | |
1 | 2 | 3 | | | | | | 4100.00
1 | 2 | 3 | | | | | | 3100.00
1 | 2 | 3 | | | | | -100.00 |
1 | 2 | 3 | | | | | 250.00 |
2 | 7 | 8 | 4 | | | | |
(8 rows)
-- I want this result (where ppv and tppv are summed and the other distinct values are boiled down into one row)
-- I want to avoid writing explicit UNIONs that will break if, say the "cid" was entered as a discreet row from the row containing "iac"
-- in this example "rspid" and "nspid" are always the same for a given ID, however they could possibly be absent for a given row as well
id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv
----+-------+-------+-----+-----+------+------+---------+---------
1 | 2 | 3 | 4 | t | 100 | 200 | 150.00 | 7200.00
2 | 7 | 8 | 4 | | | | 0.00 | 0.00
I have experimented with doing the aggregates as a CTE and then joining that to various incarnations of DISTINCT and DISTINCT ON, but those do not do what I want. Trying to find the right combination of terms to get an answer from Google has been unfruitful.
Any ideas?
Thank you for your consideration.
Bill MacArthur
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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: webmaster@dhs-club.com
Subject: Re: reduce many loosely related rows down to one
In-Reply-To: <51A0660A.2010806@dhs-club.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox