Calculating sum of children's balance for each parent

debugcn Published at Dev

Hatik

I have two tables having parent-child relationship

Table1
| Account_no | Main_account_no | bla bla
+------------+-----------------+----------
|     234    |        111      |  
|     235    |        111      |  
|     222    |        112      |  
|     220    |        112      |

The second:

Table2
| Account_no | Balance         | bla bla
+------------+-----------------+----------
|     234    |        10       |  
|     235    |        15       |  
|     222    |        55       |  
|     220    |        45       |

What I need is to calculate the sum of balance of child accounts for each parent account. The expected result is

| Main_account_no | Balance |
+-----------------+---------+
| 111             |  35     |
| 112             |  100    |

I am trying to use

SELECT MAIN_ACCOUNT_NO, SUM((SELECT BALANCE FROM TABLE2 WHERE ACCOUNT_NO = A.ACCOUNT_NO)) OVER (PARTITION BY MAIN_ACCOUNT_NO)
FROM TABLE1 A
GROUP BY MAIN_ACCOUNT_NO

But I get the ORA-00979: not a GROUP BY expression

Where did I do wrong?

Aidan Kane

Since this apparently isn't homework... :-)

First let's get the main_account_nos alongside the balances:

select
    Table1.Main_account_no,
    Table1.Account_no,
    Table2.Balance
from
    Table1
    join Table2 on Table1.Account_no = Table2.Account_no

Gives

| Main_account_no | Account_no | Balance         |
+-----------------+------------+-----------------+
|        111      |     234    |        10       |
|        111      |     235    |        15       |
|        112      |     222    |        55       |
|        112      |     220    |        45       |

Now we can easily group as required:

select
    Table1.Main_account_no,
    sum(Table2.Balance) as total
from
    Table1
    join Table2 on Table1.Account_no = Table2.Account_no
group by
    Table1.Main_account_no

Collected from the Internet

Please contact [email protected] to delete if infringement.