PostgreSQL Query – Generate Balance from Chart of Accounts

postgresqlquery

I have the following tables in postgres

 ChartOfAccounts
| Id          | int     |
| AccountName | varchar |
| ParentId    | int     | FK self references Id
| Level       | int     | Check constraint Level >= 1 && Level <= 4

 Transactions
| Id          | int     |
| Amount      | int     |
| AccountId   | int     | FK references Accounts(Id)

The Chart of Accounts is a tree structure four levels deep, and a transaction will only ever reference the fourth level.

I need to be able to find the balance (sum of all trasaction amounts) of any or multiple accounts in the Chart of Accounts, no matter what level.

I've been able to come up with specific queries that will find the balance of a 4th level account and another for the 3th level, but not one that I can use for any level.

I also wonder if this query could be too slow when you have hundreds of thousands or even millions of rows in the Transactions table, but I'm guessing as long as its well indexed it will be ok.

edit:

The user can only add level 4 accounts, levels 1 to 3 are locked in and can't be changed.

I want to be able to query these balances by account_id, heres the query I currently have for level 3 account balance.

SELECT a.parent_id, SUM(t.amount) balance 
FROM transaction t 
JOIN account a ON t.account_id = a.id 
WHERE a.parent_id IN (1, 4, 7) -- IDs of the accounts which I want the balance
GROUP BY a.parent_id;

The expected output is

| account_id | balance |
| 1          | 1500    |
| 4          | -2500   |
| 7          | 550     |

Best Answer

You need a hierarchical or recursive query, like

with recursive
 startwith(val) as (
  select 2
  union select 5
 ), AccountTree AS (
   SELECT
      id,
      ParentId,
      id as tree_start,
      name
    FROM ChartOfAccounts
    WHERE id in (select val from startwith)
   UNION
   SELECT
      ca.id,
      ca.ParentId,
      s.tree_start,
      ca.name
     FROM ChartOfAccounts ca
     INNER JOIN AccountTree s ON s.id = ca.ParentId
)
SELECT tree_start, sum(t.amount)
  FROM AccountTree at
  left outer join Transactions t on t.AccountId = at.id
  group by tree_start

See this DB Fiddle

Related Question