Average Value on Date Time per second SQL

Question

I have the following table:

oDateTime              Value
----------------------------------------------
2017-01-01 00:00:00    10
2017-01-01 00:00:01    20
2017-01-01 00:00:02    10
2017-01-01 00:00:03    10
ff.

The row data is per second on each date. I want to have the following result:

oDateTime              Value
----------------------------------------------
2017-01-01 01:00:00    10

So it's the hourly average for each date.
Any idea how to do this?

Thank you.

Answer 1

DATEADD(HOUR, DATEDIFF(HOUR, 0, oDateTime), 0) will round any DATETIME value, down to the hour. This enables you to keep the DateHour value as a single column.

SELECT
  DATEADD(HOUR, DATEDIFF(HOUR, 0, oDateTime), 0),
  SUM(value) / 60.0,
  AVG(value)
FROM
  yourTable
GROUP BY
  DATEADD(HOUR, DATEDIFF(HOUR, 0, oDateTime), 0)

EDIT: Less repetition, but slightly longer...

SELECT
  rounded.DateHour,
  SUM(value) / 60.0,
  AVG(value)
FROM
  yourTable
CROSS APPLY
  (VALUES(DATEADD(HOUR, DATEDIFF(HOUR, 0, oDateTime), 0))) AS rounded(dateHour)
GROUP BY
  rounded.DateHour