SQL CE give back summed values of timeranges-CodePudding

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?

CodePudding user response：

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