Accounting Balances. Hierarchial Balances and Rollup

Question

Accounts (Master List of Accounts with link to their parent (same table) ) (Accounts table is maintained using nested_set plugin, so the left, right, depth are available in the table and are maintained while adding/editing accounts)

| id | name           | parent_id |
|----|----------------|-----------|
| 1  | Assets         | null      |
| 2  | Current Assets | 1         |
| 3  | Fixed Assets   | 1         |
| 4  | Bank           | 2         |
| 5  | Bank One       | 4         |
| 6  | Bank Two       | 4         |
| 7  | Revenue        | null      |
| 8  | Sales          | 7         |
| 9  | Expenses       | null      |
| 10 | Rent           | 9         |

Entries (where the date and description of each transaction stored)

| entry_id | date       | description        |
|----------|------------|--------------------|
| 1        | Mar 3 2020 | Cash Sales         |
| 2        | Mar 3 2020 | Cash Paid For Rent |
| 3        | Apr 1 2020 | Owner Withdrawal   |

Amounts (where the double entry transactions are stored)

| entry_id | account_id | type   | amount |
|----------|------------|--------|--------|
| 1        | 5          | debit  | 10000  |
| 1        | 8          | credit | 10000  |
| 2        | 10         | debit  | 1000   |
| 2        | 5          | credit | 1000   |
|          |            |        |        |

Given the above structure, here is my requirements

1. Arrange the accounts in Tree(hierarchical) structure and calculate the individual account balances (balances can be debit_balance or credit_balance)
2. Hierarchical account balances, rolling up child balances to the parent accounts

PS: I do have the solution for the req 1 above using a combination of WITH RECURSIVE sql function on the accounts table and arranging the rows hierarchially and then joining the result set with amounts table that is summed up on amount column (after grouping on type) for each accounts. I am keen to see how the folks over here will solve this. (lemme know if you would like to see what I got so far though)

here is the query that gets me the first result set. (i've omitted the details like normal_credit_blance flag etc for brevity in the original question)

        select id, parent_id, name, newdepth as depth, debit_amount, credit_amount, type,
        CASE WHEN normal_credit_balance = true THEN  credit_amount - debit_amount END as credit_balance,
            CASE WHEN normal_credit_balance = false THEN  debit_amount - credit_amount END as debit_balance

        from
        (
        WITH RECURSIVE children AS (
        SELECT id, parent_id, display_name, lft, rgt, type, normal_credit_balance, 0 as newdepth
        FROM accounts
        WHERE parent_id is null 
        UNION
        SELECT op.id, op.parent_id, op.display_name, op.lft, op.rgt, op.type, op.normal_credit_balance, newdepth + 1
        FROM accounts op
        JOIN children c ON op.parent_id = c.id
        )
        SELECT *
        FROM children 
        ) accounts_tbl 
        left join 

        ( SELECT  account_id, 
        SUM( CASE WHEN am.type =  'debit' THEN COALESCE( AMOUNT , 0.0 ) ELSE 0.0 END ) AS debit_amount ,
          SUM( CASE WHEN am.type =  'credit' THEN   COALESCE( AMOUNT , 0.0 ) ELSE 0.0 END ) AS credit_amount
          FROM amounts am
        join accounts ac on ac.id = am.account_id
          group by account_id, ac.name, ac.type ) 
          
        as  amount_tbl  

        on accounts_tbl.id = amount_tbl.account_id order by lft

sample result based on the amounts table entries, the rollup should look like this:

| id | name           | balance   |  
|----|----------------|-----------|            
| 1  | Assets         | 9000      |            
| 2  | Current Assets | 9000      |            
| 3  | Fixed Assets   | 0         |            
| 4  | Bank           | 9000      |            
| 5  | Bank One       | 9000      |            
| 6  | Bank Two       | 0         |            
| 7  | Revenue        | 10000     |
| 8  | Sales          | 10000     |
| 9  | Expenses       | 1000      |
| 10 | Rent           | 1000      |

Answer 1

I would start by computing the "direct" balance of each account, with a left join and aggregation. Then goes the recursive query: you just need to traverse the tree from the leafs to the root, conslidating the balance as you go. The final step is aggregation.

with recursive
    data (id, name, parent_id, balance) as (
        select 
            ac.*, 
            coalesce(sum(case am.type when 'debit' then - amount when 'credit' then amount end), 0) balance
        from accounts ac
        left join amounts am on am.account_id = ac.id
        group by ac.id
    ),
    cte (id, name, parent_id, balance) as (
        select d.* from data d
        union all
        select d.id, d.name, d.parent_id, d.balance + c.balance
        from cte c
        inner join data d on d.id = c.parent_id
    )
select id, name, sum(balance) from cte group by id, name

I don't get how all the accounts in your resultset end up with a positive balance, while some have more debits than credit (and vice-versa). The query treats debits as negative amounts and credits as positive.

Demo on DB Fiddle:

id | name           |   sum
-: | :------------- | ----:
 1 | Assets         | -9000
 2 | Current Assets | -9000
 3 | Fixed Assets   |     0
 4 | Bank           | -9000
 5 | Bank One       | -9000
 6 | Bank Two       |     0
 7 | Revenue        | 10000
 8 | Sales          | 10000
 9 | Expenses       | -1000
10 | Rent           | -1000

Answer 2

If you were using a closure table instead of nested sets (like I do in https://stackoverflow.com/a/38701519/5962802) then you could use simple JOINs like

SELECT 
  accounts.id, 
  accounts.title, 
  SUM(COALESCE(debits.amount,0)) AS debit, 
  SUM(COALESCE(credit.amount,0)) AS credit
FROM account_tree
LEFT JOIN accounts ON ancestor_id = accounts.id
LEFT JOIN balances AS debits ON account_id = child_id AND type = 'debit'
LEFT JOIN balances AS credits ON account_id = child_id AND type = 'credit'
GROUP BY accounts.id,accounts.title

As a side note I would recommend you to keep debits and credits on the same row - as 2 columns in table BALANCES.