Summing Row in SQL query for time range
- by user3703334
I'm trying to group a large amount of data into smaller bundles.
Currently the code for my query is as follows
SELECT [DateTime]
,[KW]
FROM [POWER]
WHERE datetime >= '2014-04-14 06:00:00' and datetime < '2014-04-21 06:00:00'
ORDER BY datetime
which gives me
DateTime KW
4/14/2014 6:00:02.0 1947
4/14/2014 6:00:15.0 1946
4/14/2014 6:00:23.0 1947
4/14/2014 6:00:32.0 1011
4/14/2014 6:00:43.0 601
4/14/2014 6:00:52.0 585
4/14/2014 6:01:02.0 582
4/14/2014 6:01:12.0 580
4/14/2014 6:01:21.0 579
4/14/2014 6:01:32.0 579
4/14/2014 6:01:44.0 578
4/14/2014 6:01:53.0 578
4/14/2014 6:02:01.0 577
4/14/2014 6:02:12.0 577
4/14/2014 6:02:22.0 577
4/14/2014 6:02:32.0 576
4/14/2014 6:02:42.0 578
4/14/2014 6:02:52.0 577
4/14/2014 6:03:02.0 577
4/14/2014 6:03:12.0 577
4/14/2014 6:03:22.0 578
.
.
.
.
4/21/2014 5:59:55.0 11
Now there is a reading every 10 seconds from a substation. Now I want to group this data into hourly readings.
Thus 00:00-01:00 = sum([KW]] for where datetime >= '^date^ 00:00:00' and datetime < '^date^ 01:00:00'
I've tried using a convert to change the datetime into date and time field and then only to add all the time fields together with no success.
Can someone please assist me, I'm not sure what is right way of doing this. Thanks
ADDED
Ok so the spilt between Datetime is working nicely, but as if I add a SUM([KW]) function SQL gives an error. And if I include any of the group functions it also nags.
Below is what works, I still need to sum the KW per the grouping of hours.
I've tried using Group By Hour and Group by DATEPART(Hour,[DateTime])
Both didn't work.
SELECT DATEPART(Hour,[DateTime]) Hour
,DATEPART(Day,[DateTime]) Day
,DATEPART(Month,[DateTime]) Month
,([KVAReal])
,([KVAr])
,([KW])
FROM [POWER].[dbo].[IT10t_PAC3200]
WHERE datetime >= '2014-04-14 06:00:00' and datetime < '2014-04-21 06:00:00'
order by datetime
