Running total for each entry in group by

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 r开发者_如何学JAVAunning totals?

---

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

---

You need to look at consecutive records, and count the time if the previous entry was 'down'. In SQL Server, you can do this... I think it's similar in mysql

With OrderedRows AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY System ORDER BY StatusTime) AS RowNum
FROM yourTable
)
SELECT o_this.System, 
  SUM(DATEDIFF(second, o_this.StatusTime, o_next.StatusTime)) AS DownTimeSeconds
FROM OrderedRows o_this
  JOIN
  OrderedRows o_next
  ON o_next.System = o_this.System
  AND o_next.RowNum = o_this.RowNum + 1
WHERE o_this.Status = 'down'
GROUP BY o_this.System;

Without ranking functions, try something like:

SELECT t.System, 
  SUM(DATEDIFF('s', 
      t.StatusTime, 
      (SELECT MIN(t_next.StatusTime) 
       FROM yourTable AS t_next
       WHERE t_next.System = t.System
       AND t_next.StatusTime > t.StatusTime
      )
   )) AS DownTimeSeconds
FROM yourTable as t
WHERE t.Status = 'down'
GROUP BY t.System;

But event this might have trouble with it being an aggregate over an aggregate.