show sum and difference in report

This topic is locked

show sum and difference in report	2/10/2012 3:41:36 AM
PHPRunner General questions
C chanpeter88 author I am using phprunner 6.0. I have two tables for accounting purpose. one is expense. the db structure is -expense_amount $ -invoice_date (format : yyyy-mm-dd) other is income. the db structure is -income_amount $ -invoice_date (format : yyyy-mm-dd) I want to calculate the profit over period. report will show : Year 2010 total expense : $xxxx (sum of expense_amount for period of 2010) total income : $xxxxx (sum of invoice_amount for period of 2010) net profit : $xxxx (calculated by total income - total expense) Year 2011 total expense : $xxxx total income : $xxxxx net profit : $xxxx How can I use your report function ?


J	Jane 2/10/2012
	Hi, you need to use ORDER BY statement and aggregate functions in your SQL query. Here is just a sample: `select sum(expense_amount) as sum_expense_amount, sum(income_amount) as sum_income_amount, Year(table1.invoice_date) as year_invoice_date from table1, table2 where Year(table1.invoice_date)=Year(table2.invoice_date) GROUP BY Year(table1.invoice_date)` More info: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html