pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Cascading sum in tree with CTE? 3+ messages / 3 participants [nested] [flat]
* Cascading sum in tree with CTE? @ 2010-04-09 07:17 Svenne Krap <svenne.lists@krap.dk> 0 siblings, 2 replies; 3+ messages in thread From: Svenne Krap @ 2010-04-09 07:17 UTC (permalink / raw) To: pgsql-sql Hi . My problem resembles this: I have the following (simplified) tables 1) create table account ( id serial, name varchar, parent_id int4 references account, primary key (id)) 2) create table transaction (id serial, account_id int4 references account, memo varchar, debet, credit, primary key(id)) So a basic ledger system, with a tree of accounts and transactions on them. Some accounts have transactions others doesn't. I have written a CTE which generates the tree, but I would like to append to each line the sum of all debits and credits of this and all sub accounts. I think it sould be doable, but cannot bend my brain around it and my google-fu has come out short. Say the data was: Account: 1, 'Expenses', null 2, 'IRS', 1 3, '7-Eleven' Transaction: 1, 2, 'Tax Jan 10', null, 100 2, 2, 'Tax Feb 10', null, 120 3, 2, 'Tax Feb 10 correction', 10,null 4, 3, 'Sodas', 10, null 5, 3, 'Beer', 5, null I would expect a tree like: 1, Debt, 25, 220 2, IRS, 10, 220 3, 7-eleven, 15, 0 Is there any way around a writing a stored procedure for that? My current CTE works top down ( top > Expenses > 7-elven) and writes out "paths" to the leaves (ie "Expenses -> 7-Eleven" instead of just 7-Eleven) Svenne ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cascading sum in tree with CTE? @ 2010-04-09 09:12 silly sad <sad@bankir.ru> parent: Svenne Krap <svenne.lists@krap.dk> 1 sibling, 0 replies; 3+ messages in thread From: silly sad @ 2010-04-09 09:12 UTC (permalink / raw) To: pgsql-sql to select a whole subtree of a particular node of a tree. u have to modify the tree representation in one of the two ways (according to you fine-tuned needs) (1) store in the EACH node the PATH from root to this node (2) store (l,r) segment representing the INCLUSIONS of nodes into other nodes subtree (exactly as segments include each other) (2) i forgot the "official" name of this type of tree representation. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cascading sum in tree with CTE? @ 2010-04-09 10:51 Harald Fuchs <hari.fuchs@gmail.com> parent: Svenne Krap <svenne.lists@krap.dk> 1 sibling, 0 replies; 3+ messages in thread From: Harald Fuchs @ 2010-04-09 10:51 UTC (permalink / raw) To: pgsql-sql In article <4BBED49D.7080501@krap.dk>, Svenne Krap <svenne.lists@krap.dk> writes: > Hi . > My problem resembles this: > I have the following (simplified) tables > 1) create table account ( id serial, name varchar, parent_id int4 > references account, primary key (id)) > 2) create table transaction (id serial, account_id int4 references > account, memo varchar, debet, credit, primary key(id)) > So a basic ledger system, with a tree of accounts and transactions on > them. Some accounts have transactions others doesn't. > I have written a CTE which generates the tree, but I would like to > append to each line the sum of all debits and credits of this and all > sub accounts. > I think it sould be doable, but cannot bend my brain around it and my > google-fu has come out short. > Say the data was: > Account: > 1, 'Expenses', null > 2, 'IRS', 1 > 3, '7-Eleven' > Transaction: > 1, 2, 'Tax Jan 10', null, 100 > 2, 2, 'Tax Feb 10', null, 120 > 3, 2, 'Tax Feb 10 correction', 10,null > 4, 3, 'Sodas', 10, null > 5, 3, 'Beer', 5, null > I would expect a tree like: > 1, Debt, 25, 220 > 2, IRS, 10, 220 > 3, 7-eleven, 15, 0 > Is there any way around a writing a stored procedure for that? How about an ancestor table? WITH RECURSIVE tree (id, anc) AS ( SELECT id, id FROM account UNION ALL SELECT a.id, t.anc FROM account a JOIN tree t ON t.id = a.parent_id ) SELECT a.id, a.name, sum(x.debet) AS debet, sum(x.credit) AS credit FROM account a JOIN tree t ON t.anc = a.id LEFT JOIN transaction x ON x.account_id = t.id GROUP BY a.id, a.name ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-04-09 10:51 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2010-04-09 07:17 Cascading sum in tree with CTE? Svenne Krap <svenne.lists@krap.dk> 2010-04-09 09:12 ` silly sad <sad@bankir.ru> 2010-04-09 10:51 ` Harald Fuchs <hari.fuchs@gmail.com>
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