2

I am getting data for 15 minutes interval. I would like to aggregate this data to hourly.Can anybody advise how should i do this query to sum the interval values and display it?

enter image description here

4

You can do a simple GROUP BY with DATEPART applied to the time column. I'm assuming that you'll always get a row every 15 minutes and you don't need to worry about gaps in the data.

SELECT 
  code
, date_column AS [date]
, DATEPART(HOUR, time_column) AS hourly
, SUM(value) AS value
FROM test_table
GROUP BY
  code
, date_column
, DATEPART(HOUR, time_column);

The following references may be useful to you:

3

In light of sum of every N hours as @wmasmaddy mentioned in the comment, here is the generic solution based on Joe's answer

declare @N int = 2;--change to your requirement
SELECT 
 code
, date_column AS [date]
, DATEPART(HOUR, time_column)/N*N +(N-1) AS N_hourly -- +(N-1) is to make the last hour as the identity hour, you can change to your own preference.
, SUM(value) AS value
FROM test_table
GROUP BY
  code
, date_column
, DATEPART(HOUR, time_column);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.