Show cumulative totals in new column
I am working on a simple flow statement where the user inputs +ve and -ve values and they are stored in a single table.
The table structure is
funds_T (date, type, amount)
when I run a select all query, it would return
Date | Type | Amount 2011-10-01 opening 26000.00 2011-11-20 payment -2000.00 2011-10-10 receipt 6000.00 2011-10-09 expense -8000.00
What I want is another column next to this which shows cumulative total, something like this.
Date | Type | Amount | Total 2011-10-01 opening 26000.00 26000.00 2011-11-20 payment -2000.00 24000.00 2011-10-10 receipt 6000.00 30000.00 2011-10-09 expense -8000.00 22000.00
also, is there any way I can do a conditional formatting just like the way you do in Excel, if value > something (background color is red, etc)
My current query is
SELECT date,type,amount FROM funds_T WHERE date >= '$fromdate' AND date <= '$todate'
Following was the query that i user after Pentium's advise
mysql_query(" SET @total: =0; SELECT date,type,comments,amount,@total := @total + amount AS total FROM funds_T WHERE date BETWEEN '$frdate' AND '$todate' ORDER BY date ASC ");
Colors and formatting is possible in PHP, while the cumulative total can be done in SQL or in PHP. I would recomand calculating the total in the PHP at runtime in the for cycle.
If you still want in SQL you need to use User defined variables
It would be something like:
SET @total:=0; SELECT `date`, `type`, `amount`, @total := @total + `amount` AS `total` FROM funds_t WHERE `date` BETWEEN '$fromdate' AND '$todate' ORDER BY `date` ASC