From: Bill MacArthur <webmaster@dhs-club.com>
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
Date: Tue, 28 May 2013 00:09:29 -0400
Message-ID: <51A42DF9.30600@dhs-club.com> (raw)
In-Reply-To: <B6F6FD62F2624C4C9916AC0175D56D880CDF60C5@jenmbs01.ad.intershop.net>
References: <51A0660A.2010806@dhs-club.com>
<B6F6FD62F2624C4C9916AC0175D56D880CDF60C5@jenmbs01.ad.intershop.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: webmaster@dhs-club.com, M.Mamin@intershop.de
Subject: Re: reduce many loosely related rows down to one
In-Reply-To: <51A42DF9.30600@dhs-club.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox