To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: Cascading sum in tree with CTE?
Date: Fri, 09 Apr 2010 12:51:58 +0200
Lines: 59
Message-ID: <pu8w8wkiep.fsf@srv.protecting.net>
References: <4BBED49D.7080501@krap.dk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:6kf29MY8JRDE2xENOXx0MUCmbtc=

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