Search code examples
sqlsql-serverdatabasetimestampmodulo

SQL CE give back summed values of timeranges

Given a table with following entries (simplified to get the idea)

timestamp | value 1 | value 2
60          12        0
120         13        0
180         10        12
240         8         0 
....
3600        11        0
3660        41        12
3720        14        8
....
  • Table has each minute logged for one whole year (525600 entries)
  • timestamp is primary key
  • using SQL Server Compact aka SQL CE in V4

Task is to get the summed values of each value-col of each hour, so not just row #3600 (= 1hour) value #1 : 11 (which could be done via modulo), but all previous values of this hour summed (12 + 13 + 10 + 8 + ... + 11).

Is there a SQL Query to achieve this?

Solution

  • Unless I have misinterpreted, you should just be able to do this with a GROUP BY, assuming that timestamp is an integer

    SELECT [timestamp]/3600 AS HourNumber, 
    SUM(value1) AS Total1, 
    SUM(value2) AS Total2 
FROM #data 
GROUP BY [timestamp]/3600

    I've not used SQL CE, but I can't imagine it would work any differently on something so fundamental as integer arithmetic and basic grouping