ansaurus

Question

Running total for each entry in group by

Answer 1

A:

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.

Rob Farley 2010-01-28 05:40:36

MySQL doesn't have any ranking/analytic functions

OMG Ponies 2010-01-28 05:42:23

Ah - hoped that wasn't the case...

Rob Farley 2010-01-28 05:58:59

I guess from the downvote, the second one doesn't work either.

Rob Farley 2010-01-28 20:26:06

Answer 2

+1 A:

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

cmroanirgo 2010-01-28 05:42:32

ansaurus

tags:

views:

answers:

Running total for each entry in group by

related questions