From: Svenne Krap <svenne.lists@krap.dk>
To: sql pgsql <pgsql-sql@postgresql.org>
Subject: Cascading sum in tree with CTE?
Date: Fri, 09 Apr 2010 09:17:49 +0200
Message-ID: <4BBED49D.7080501@krap.dk> (raw)
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
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: svenne.lists@krap.dk
Subject: Re: Cascading sum in tree with CTE?
In-Reply-To: <4BBED49D.7080501@krap.dk>
* 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