Re: Cascading sum in tree with CTE?

In article <4BBED49D(dot)7080501(at)krap(dot)dk>,
Svenne Krap <svenne(dot)lists(at)krap(dot)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