I am developing an accounting package. There are accounts and these accounts will have sub accounts and in turn those sub accounts will have sub accounts and it may grow.
I need to display sub totals of each account and adding them up to show total amount on their parent account. Like it is done in gnucash. Here is the link: http://gnucash.org/docs/v2.4/C/gnucash-help/figures/Help_AccountTree_Screen.png
Here is table: Accoun Type table
AccountID int(10) //Account ID. For root account the default is zero.
AccountName varchar(200) //Account Name
Accounttype varchar(40) //Account type: asset, liability, income, expenses, equity
Parent ID int(10)//stores the reference of parent account ID for the sub account.
Transction Posting table: PostID Bigint(20)
FinancialAccountID int(10) //Usually a reference of AccountID in Account type
EffectingAccountID int(10) //This refers to second account that effects in post
debitamount decimal (18,2)
Requirement: I want a select statement, that can fetch total of each account, added to its parent account on the top.
For reference: Kindly look at GNUCash application which displays Link: http://gnucash.org/docs/v2.4/C/gnucash-help/figures/Help_AccountTree_Screen.png
Looking forward for a response. Thanks Reuben.