Skip site navigation (1) Skip section navigation (2)

Re: grouping/clustering query

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Joe" <dev(at)freedomcircle(dot)net>,"Steve Midgley" <science(at)misuse(dot)org>
Cc: <pgsql-sql(at)postgresql(dot)org>,"David Garamond" <davidgaramond(at)gmail(dot)com>
Subject: Re: grouping/clustering query
Date: 2008-10-24 15:20:02
Message-ID: 014601c935eb$fc9a5810$ (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
Your script is handy, Steve.

Spontaneously, This seems to be an array type problem, something I just have 
vague notions about.

I'll take a look at this, to see if something 


----- Original Message ----- 
From: "Steve Midgley" <science(at)misuse(dot)org>
To: "Joe" <dev(at)freedomcircle(dot)net>
Cc: <pgsql-sql(at)postgresql(dot)org>; "David Garamond" <davidgaramond(at)gmail(dot)com>
Sent: Friday, October 24, 2008 4:04 PM
Subject: Re: [SQL] grouping/clustering query

> At 11:28 AM 10/23/2008, Joe wrote:
>>Steve Midgley wrote:
>>>># (invoiceid, txid)
>>>>(A, 1)
>>>>(A, 3)
>>>>(B, 1)
>>>>(B, 2)
>>>>(C, 5)
>>>>(D, 6)
>>>>(D, 7)
>>>>(E, 8)
>>>>(F, 8)
>>>>For journalling, I need to group/cluster this together. Is there a SQL
>>>>query that can generate this output:
>>>># (journal: invoiceids, txids)
>>>>[A,B] , [1,2,3]
>>>>[C], [5]
>>>>[D], [6,7]
>>>>[E,F], [8]
>>>Hi Dave,
>>>I'm not following the logic here. A has 1,3 and B has 1,2. So why does 
>>>the first line print:
>>>>[A,B] , [1,2,3]
>>>What's the rule that tells the query to output this way? Is it that all 
>>>of B's values are between A's values?
>> From a purely accounting standpoint, since transaction 1 was applied to 
>> both invoices A and B, you need to group the invoices so that you can 
>> compare total invoiced against total paid.
> I tinkered around briefly but didn't come up with a good idea, but I bet 
> someone on this list can. However, I did create a CREATE script for your 
> table design which, in my experience, makes it more likely that a real 
> expert will take on your problem..
> Hope this helps,
> Steve
> (
>   id serial NOT NULL,
>   inv_id character varying,
>   tx_id character varying,
> )
> insert into trans (inv_id, tx_id) values('A','1');
> insert into trans (inv_id, tx_id) values('A','3');
> insert into trans (inv_id, tx_id) values('B','1');
> insert into trans (inv_id, tx_id) values('B','2');
> insert into trans (inv_id, tx_id) values('C','5');
> insert into trans (inv_id, tx_id) values('D','6');
> insert into trans (inv_id, tx_id) values('D','7');
> insert into trans (inv_id, tx_id) values('E','8');
> insert into trans (inv_id, tx_id) values('F','8');
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:

In response to

pgsql-sql by date

Next:From: Tony WassonDate: 2008-10-24 17:24:55
Subject: Re: grouping/clustering query
Previous:From: Steve MidgleyDate: 2008-10-24 15:04:37
Subject: Re: grouping/clustering query

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group