We help IT Professionals succeed at work.

Sum value by hour in sql

62 Views
Last Modified: 2019-01-22
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

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Scott AbrahamTechnical Systems Manager

Author

Commented:
Thanks to both of you!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions