## Re: grouping/clustering query

From: Steve Midgley Joe pgsql-sql(at)postgresql(dot)org,David Garamond Re: grouping/clustering query 2008-10-24 15:04:37 20081024150447.92B2F64FD19@postgresql.org (view raw or flat) 2008-10-23 16:08:48 from Steve Midgley  2008-10-23 18:28:33 from Joe   2008-10-24 15:04:37 from Steve Midgley    2008-10-24 15:20:02 from "Oliveiros Cristina"    2008-10-24 17:24:55 from "Tony Wasson"     2008-10-24 20:48:51 from "Tony Wasson"      2008-10-25 01:50:11 from "David Garamond"       2008-10-27 19:28:09 from "Osvaldo Kussama" 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');

```

### pgsql-sql by date

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