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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql