Skip navigation links

MySQL Forums :: PHP :: Mysql Displaying subtotals and totals


Advanced Search

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 Reuben Kumar 02/05/2013 03:45AM
Re: Mysql Displaying subtotals and totals Rick James 02/06/2013 08:01PM
Re: Mysql Displaying subtotals and totals Reuben Kumar 02/06/2013 11:24PM
Re: Mysql Displaying subtotals and totals Peter Brawley 02/08/2013 10:42AM


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.