From: Harald Fuchs <hari.fuchs@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Cascading sum in tree with CTE?
Date: Fri, 09 Apr 2010 12:51:58 +0200
Message-ID: <pu8w8wkiep.fsf@srv.protecting.net> (raw)
References: <4BBED49D.7080501@krap.dk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hari.fuchs@gmail.com
Subject: Re: Cascading sum in tree with CTE?
In-Reply-To: <pu8w8wkiep.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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