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

Re: grouping/clustering query

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>
Subject: Re: grouping/clustering query
Date: 2008-10-24 15:04:37
Message-ID: 20081024150447.92B2F64FD19@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-sql
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

DROP TABLE IF EXISTS trans;

CREATE TABLE trans
(
   id serial NOT NULL,
   inv_id character varying,
   tx_id character varying,
   CONSTRAINT pk_id PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

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');




In response to

Responses

pgsql-sql by date

Next:From: Oliveiros CristinaDate: 2008-10-24 15:20:02
Subject: Re: grouping/clustering query
Previous:From: JoeDate: 2008-10-23 18:28:33
Subject: Re: grouping/clustering query

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