Group by 2 mysql pieces

Question

Mysql table:

enter image description here

I want to take the amount of acceleCount at intervals of 2 minutes.

select  time div 120000 as TwoMinutes,                 
sum(acceleCount) as Sum
from acceleTable
group by time div 120000

enter image description here

Here, the timestamp of column twoMinutes is less. I want it to have a time stamp that is within two minutes.

Any thoughts on how to modify the SQL query?

+3

2 answers

A small optimized way to do it

SELECT    (ROUND(time / 120000) * 120000)  AS timekey, sum(acceleCount)
FROM       acceleTable
GROUP BY   timekey

+1

Abhishek gupta 22 Sep 14 at 13:20

DanFromGermany · Accepted Answer · 2014-09-22T12:12:42+0000

Bring the timestamps to a common denominator by division, rounding and multiplication:

SELECT
(ROUND(time / 120) * 120),
sum(acceleCount)
FROM acceleTable
GROUP BY (ROUND(time / 120) * 120)