Re: reduce many loosely related rows down to one - Mailing list pgsql-sql
From | Marc Mamin |
---|---|
Subject | Re: reduce many loosely related rows down to one |
Date | |
Msg-id | B6F6FD62F2624C4C9916AC0175D56D880CDF60C5@jenmbs01.ad.intershop.net Whole thread Raw |
In response to | reduce many loosely related rows down to one (Bill MacArthur <webmaster@dhs-club.com>) |
Responses |
Re: reduce many loosely related rows down to one
|
List | pgsql-sql |
> ________________________________________ > Von: pgsql-sql-owner@postgresql.org [pgsql-sql-owner@postgresql.org]" im Auftrag von "Bill MacArthur [webmaster@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 thatdo 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 rowcontaining "iac" > -- in this example "rspid" and "nspid" are always the same for a given ID, however they could possibly be absent for agiven 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 DISTINCTON, but those do not do what I want. Trying to find the right combination of terms to get an answer from Google hasbeen 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