CODEWITHSUNDEEP
sqlt-sqlsql-server-2014
Haminteu
Haminteu

Reputation: 1334

Average Value on Date Time per second SQL

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.

Upvotes: 0

Views: 148

Answers (2)

MatBailie
MatBailie

Reputation: 86706

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Try aggregating by the date and hour:

SELECT
    CONVERT(varchar, oDateTime, 112) AS date,
    DATEPART(hour, oDateTime) AS hour,
    AVG(Value) AS Value
FROM yourTable
GROUP BY
    CONVERT(varchar, oDateTime, 112),
    DATEPART(hour, oDateTime);

Upvotes: 0

Related Questions