In article <4bbed49d.7080...@krap.dk>, Svenne Krap <svenne.li...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql