Sum value by hour in sql
I have table data that queries like this:
OBJECT TS VALUE
1 1/21/19 0:15 12
1 1/21/19 0:30 18
1 1/21/19 0:45 12
1 1/21/19 1:00 18
1 1/21/19 1:15 12
1 1/21/19 1:30 12
1 1/21/19 1:45 18
1 1/21/19 2:00 12
1 1/21/19 2:15 12
1 1/21/19 2:30 18
1 1/21/19 2:45 12
1 1/21/19 3:00 12
1 1/21/19 3:15 18
1 1/21/19 3:30 12
1 1/21/19 3:45 12
1 1/21/19 4:00 18
I want to query it out so it adds all values into an hourly value. For example the 1:00 hour is a sum of the 00:15,00:30,00:45, and 01:00.
It queries out like this:
OBJECT TS VALUE
1 1/21/19 1:00 18 60
1 1/21/19 2:00 12 54
1 1/21/19 3:00 12 54
1 1/21/19 4:00 18 60
Any help would be appreciated.
OBJECT TS VALUE
1 1/21/19 0:15 12
1 1/21/19 0:30 18
1 1/21/19 0:45 12
1 1/21/19 1:00 18
1 1/21/19 1:15 12
1 1/21/19 1:30 12
1 1/21/19 1:45 18
1 1/21/19 2:00 12
1 1/21/19 2:15 12
1 1/21/19 2:30 18
1 1/21/19 2:45 12
1 1/21/19 3:00 12
1 1/21/19 3:15 18
1 1/21/19 3:30 12
1 1/21/19 3:45 12
1 1/21/19 4:00 18
I want to query it out so it adds all values into an hourly value. For example the 1:00 hour is a sum of the 00:15,00:30,00:45, and 01:00.
It queries out like this:
OBJECT TS VALUE
1 1/21/19 1:00 18 60
1 1/21/19 2:00 12 54
1 1/21/19 3:00 12 54
1 1/21/19 4:00 18 60
Any help would be appreciated.
Hi
Here is a very simple solution, but the boundaries of what you're asking for a slightly different.
You wanted 0:15, 0:30, 0:45, 1:00 to return as 1:00. In my code below 0:00, 0:15, 0:30, 0:45 return as 0:00.
HTH
David
Here is a very simple solution, but the boundaries of what you're asking for a slightly different.
You wanted 0:15, 0:30, 0:45, 1:00 to return as 1:00. In my code below 0:00, 0:15, 0:30, 0:45 return as 0:00.
HTH
David
use EE
go
if object_id( N'tempdb..#t', N'U' ) is not null
drop table #t;
create table #t(
Object int
, ts datetime
, value int
)
insert #t values
( 1, '1/21/19 0:15', 12 )
, ( 1, '1/21/19 0:30', 18 )
, ( 1, '1/21/19 0:45', 12 )
, ( 1, '1/21/19 1:00', 18 )
, ( 1, '1/21/19 1:15', 12 )
, ( 1, '1/21/19 1:30', 12 )
, ( 1, '1/21/19 1:45', 18 )
, ( 1, '1/21/19 2:00', 12 )
, ( 1, '1/21/19 2:15', 12 )
, ( 1, '1/21/19 2:30', 18 )
, ( 1, '1/21/19 2:45', 12 )
, ( 1, '1/21/19 3:00', 12 )
, ( 1, '1/21/19 3:15', 18 )
, ( 1, '1/21/19 3:30', 12 )
, ( 1, '1/21/19 3:45', 12 )
, ( 1, '1/21/19 4:00', 18 )
select *
from #t
select
dateadd( hour, datediff( hour, 0, t.ts ), 0 )
, sum( value )
from #t t
group by
dateadd( hour, datediff( hour, 0, t.ts ), 0 )
order by
dateadd( hour, datediff( hour, 0, t.ts ), 0 )
;
-- results
2019-01-21 00:00:00.000 42
2019-01-21 01:00:00.000 60
2019-01-21 02:00:00.000 54
2019-01-21 03:00:00.000 54
2019-01-21 04:00:00.000 18
Hi,
Scott's answer is the same as mine apart from an order by clause, but I've got a little more detail and the explanation that the results are slightly different from what you requested.
HTH
David
Scott's answer is the same as mine apart from an order by clause, but I've got a little more detail and the explanation that the results are slightly different from what you requested.
HTH
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both of you!
FROM ...
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, TS), 0)