Message-ID: <51A42DF9.30600@dhs-club.com>
Date: Tue, 28 May 2013 00:09:29 -0400
From: Bill MacArthur <webmaster@dhs-club.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:17.0) Gecko/20130509 Thunderbird/17.0.6
MIME-Version: 1.0
To: Marc Mamin <M.Mamin@intershop.de>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: reduce many loosely related rows down to one
References: <51A0660A.2010806@dhs-club.com>
	<B6F6FD62F2624C4C9916AC0175D56D880CDF60C5@jenmbs01.ad.intershop.net>
In-Reply-To: 
 <B6F6FD62F2624C4C9916AC0175D56D880CDF60C5@jenmbs01.ad.intershop.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


On 5/25/2013 7:57 AM, Marc Mamin wrote:
>
>> ________________________________________
>> Von: pgsql-sql-owner@postgresql.org [pgsql-sql-owner@postgresql.org]&quot; im Auftrag von &quot;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 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