1

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     |
4
  • 1
    I think your answer depends on the way you allow your users to input requests. And business requirements. Do they need to be able to enter all 4 parts of the string? Just one part? Any number of 1 to 4 parts? Open transactions, posted, or both? Users most likely want to query by account name, what if there are duplicates? (there will be). There probably is not a one size fits all solution here. Which is why you are having difficulty finding a query that works for any level.
    – Jacob H
    Mar 2, 2020 at 18:01
  • How to ensure that there are no rings in the table ChartOfAccounts? FK + CHECK shown is not enough...
    – Akina
    Mar 2, 2020 at 18:24
  • @Akina I've edited the question to add more context. There can't be rings since the user can only create level 4 accounts.
    – koi
    Mar 2, 2020 at 18:38
  • @JacobH I want to be able to get the balance of one or all accounts if needed by account id.
    – koi
    Mar 2, 2020 at 18:38

1 Answer 1

2

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

5
  • Thats very close to what I need! Now I just need to be able to query by more than one single id, similar to the example I showed in my edit, which will probably require a GROUP BY clause. I've been trying to tweak your solution but without much success.
    – koi
    Mar 2, 2020 at 19:31
  • See my edited answer. You may need to study SQL some more. Fiddle has been updated too. Mar 2, 2020 at 20:20
  • I definitely need to, I cant do much more than insert/update/delete and simple joins. Had never played with recursive before. It doesn't work like I expected it to though, I need to results to be the individual balances of the account of id 2, and 5, not the sum of their balances.
    – koi
    Mar 2, 2020 at 20:53
  • So you need to select and group by the parent of each tree. I'll have a look if postgresql allows that. Mar 4, 2020 at 11:34
  • It seems to. Check it out at the updated fiddle. Mar 4, 2020 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.