Sum value by hour in sql

Scott Abraham
Scott Abraham used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, TS), 0) AS TS_HOUR, SUM(VALUE)
FROM ...
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, TS), 0)
David ToddSenior Database Administrator

Commented:
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

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

Open in new window

David ToddSenior Database Administrator

Commented:
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
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Yeah, quite true.  If you want to roll forward to the next hour, you'll need to add 45 mins to the original time:

SELECT      DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(MINUTE, 45, ts)), 0) AS TS_HOUR
...
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(MINUTE, 45, ts)), 0)
Scott AbrahamTechnical Systems Manager

Author

Commented:
Thanks to both of you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial