Re: reduce many loosely related rows down to one - Mailing list pgsql-sql

On 5/25/2013 7:57 AM, Marc Mamin wrote:
>
>> ________________________________________
>> 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
>
Ya, there is more to the picture than I described. Didn't want to bore with excessive detail. I was hoping that perhaps
somebodywould 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
forthe look anyway Marc. Your description of what I wanted was more accurate and concise than I had words for at the
timeof the night I originally posted this.
 

Have a good one.

Bill MacArthur