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.
Scott AbrahamTechnical Systems ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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 AdministratorCommented:
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 AdministratorCommented:
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 PletcherSenior DBACommented:
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott AbrahamTechnical Systems ManagerAuthor Commented:
Thanks to both of you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.