Running total for each entry in group by

Question

My Table structure looks like this...

| 2010-01-28 10:00:01 | Delhi | Application | up     | 
| 2010-01-28 10:00:01 | Delhi | Mysql       | up     | 
| 2010-01-28 10:00:01 | Delhi | LAN         | down   | 
| 2010-01-28 10:00:01 | Delhi | WAN         | up     | 
| 2010-01-28 10:15:01 | Delhi | Application | up     | 
| 2010-01-28 10:15:01 | Delhi | Mysql       | up     | 
| 2010-01-28 10:15:01 | Delhi | LAN         | down   | 
| 2010-01-28 10:15:01 | Delhi | WAN         | up     | 
| 2010-01-28 10:30:01 | Delhi | Application | up     | 
| 2010-01-28 10:30:01 | Delhi | Mysql       | up     | 
| 2010-01-28 10:30:01 | Delhi | LAN         | up   | 
| 2010-01-28 10:30:01 | Delhi | WAN         | up     | 

The total Downtime for LAN is 30 Minutes.

| 2010-01-28 10:00:01 | Delhi | LAN         | down   | 
| 2010-01-28 10:15:01 | Delhi | LAN         | down   | 
| 2010-01-28 10:30:01 | Delhi | LAN         | up   | 

Is there any way to calculate the total downtime the way we use calculations for running totals?

Answer 1

It appears that each reporting period is exactly 15 mins and each app is listed, so a quick hack can be:

SELECT COUNT(state)*15 as downtime,Application FROM stats WHERE state='down' GROUP BY Application