On 5/25/2013 7:57 AM, Marc Mamin wrote:
________________________________________
Von: pgsql-sql-ow...@postgresql.org [pgsql-sql-ow...@postgresql.org]" im
Auftrag von "Bill MacArthur [webmas...@dhs-club.com]
Gesendet: Samstag, 25. Mai 2013 09:19
An: pgsql-sql@postgresql.org
Betreff: [SQL] reduce many loosely related rows down to one
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.
....
-- 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.
Hello,
If I understand you well, you want to perform a group by whereas null values
are coalesced to existing not null values.
this seems to be logically not feasible.
What should look the result like if your "raw" data are as following:
id | rspid | nspid | cid | iac | newp | oldp | ppv | tppv
----+-------+-------+-----+-----+------+------+---------+---------
1 | 2 | 3 | 4 | t | | | |
1 | 2 | 3 | 5 | t | | | |
1 | 2 | 3 | | | 100 | | |
(to which cid should newp be summed to?)
regards,
Marc Mmain
Ya, there is more to the picture than I described. Didn't want to bore with excessive
detail. I was hoping that perhaps somebody would see the example and say "oh ya that
can be solved with this obscure SQL implementation" :)
I have resigned myself to using a few more CTEs with DISTINCTs and joining it
all up to get the results I want. Thanks for the look anyway Marc. Your
description of what I wanted was more accurate and concise than I had words for
at the time of the night I originally posted this.
Have a good one.
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
