Kevin Kevin - 5 months ago 9
SQL Question

select sum of max values from multiple incrementing sequences

I want to calculate a sum of the max values from sequence of increment values.

for this data set:

time_stamp count
1467820429 6 *
1467820428 5
1467820427 4
1467820426 3
1467820416 2
1467820415 1
1467820413 0
1467820412 3 *
1467820411 2
1467820409 1
1467820408 0
1467820405 1 *
1467820404 0
1467820400 5 *


answer = 6 + 3 + 1 + 5 = 15

how can i write a MySQL compatible SQL statement to acheve this

Answer
SELECT SUM(a.cnt)
  FROM 
     ( SELECT x.*
            , MIN(y.time_stamp) next
         FROM my_table x
         LEFT
         JOIN my_table y
           ON y.time_stamp > x.time_stamp
        GROUP 
           BY x.time_stamp
     ) a
  LEFT
  JOIN my_table b
    ON b.time_stamp = a.next
   AND b.cnt > a.cnt
 WHERE b.cnt IS NULL;