CODEWITHSUNDEEP
mysqlsql
Zerium
Zerium

Reputation: 17333

Find the sum of DATETIME differences within a table?

I have the following table:

---------------------------------
| id | class_id | time | status |
---------------------------------
| 1  | 1        | <>   | 1      |
---------------------------------
| 2  | 2        | <>   | 1      |
---------------------------------
| 3  | 1        | <>   | 0      |
---------------------------------
| 4  | 2        | <>   | 0      |
---------------------------------

I want a query that will see that the first row has class_id = 1 and status = 1. It will then look for the next row with class_id = 1 and status = 0, and find the time difference between the two (time is DATETIME).

At the end of all this, it will return me a sum of all time differences (i.e. (row 1 - row 3) + (row2 - row4)).

How is this possible? In generalisation, the question is about getting an aggregate total of differences between rows in a table, based off a condition.

Upvotes: 1

Views: 32

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

For every status 0 record we search the latest status 1 record. This is from all previous status 1 records take the latest.

select 
  class_id,
  sum
  (
    timestampdiff
    (
      second,
      (
        select s1.time 
        from mytable s1 
        where s1.status = 1 
        and s1.class_id = s0.class_id
        and s1.id < s0.id
        order by s1.id desc limit 1
      ),
      s0.time
    )
  ) as diffsecs
from mytable s0
where status = 0
group by class_id;

Upvotes: 1

Related Questions