Avatar of Scott Abraham
Scott Abraham
Flag for United States of America asked on

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.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Scott Abraham

8/22/2022 - Mon
Scott Pletcher

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, TS), 0) AS TS_HOUR, SUM(VALUE)
FROM ...
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, TS), 0)
David Todd

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 Todd

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Abraham

ASKER
Thanks to both of you!