Re: reduce many loosely related rows down to one

On 5/25/2013 7:57 AM, Marc Mamin wrote:
>
>> ________________________________________
>> Von: pgsql-sql-owner(at)postgresql(dot)org [pgsql-sql-owner(at)postgresql(dot)org]" im Auftrag von "Bill MacArthur [webmaster(at)dhs-club(dot)com]
>> Gesendet: Samstag, 25. Mai 2013 09:19
>> An: pgsql-sql(at)postgresql(dot)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