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 ");

Answers


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 

Need Your Help

Increasing/decreasing large sets of integers

jquery parsefloat

I'm having a little trouble with this concept. I'm attempting to write a relatively simple script which will increase/decrease a set of integers by a specified amount.

Make HTML5 header work with IE — HTML5 Shiv doesn’t fix my layout

javascript internet-explorer html5 dom

I posted the other day about problems I was having with my layout in IE.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.