MySQL Forums
Forum List  »  PHP

Mysql Displaying subtotals and totals
Posted by: Reuben Kumar
Date: February 05, 2013 03:45AM

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
PostDate datetime
debitamount decimal (18,2)
creditamount 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.

Options: ReplyQuote


Subject
Written By
Posted
Mysql Displaying subtotals and totals
February 05, 2013 03:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.