Aggregate interval data to hourly

2018-02-02 21:24:07

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?

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:

https://msdn.microsoft.com/en-us/library/ms174420.aspx

https://msdn.microsoft.com/en-us/library/ms177673.aspx

https://msdn.microsoft.com/en-us/library/ms187810.aspx

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

    https://msdn.microsoft.com/en-us/library/ms174420.aspx

    https://msdn.microsoft.com/en-us/library/ms177673.aspx

    https://msdn.microsoft.com/en-us/library/ms187810.aspx

    2018-02-02 21:41:50