CODEWITHSUNDEEP
t-sqlssmsaverage
Andrew
Andrew

Reputation: 811

Take Hourly Average in SQL

I have a SQL table with 2 fields: TimeStamp and Value. Below is an excerpt of some of the data.

2005-02-17 13:31:00     2
2005-02-17 13:46:00     3
2005-02-17 14:01:00     1.7
2005-02-17 14:16:00     2.3
2005-02-17 14:31:00     2
2005-02-17 14:46:00     2.5
2005-02-17 15:01:00     2.2
2005-02-17 15:16:00     2.4
2005-02-17 15:31:00     2.6
2005-02-17 15:46:00     2.6
2005-02-17 16:01:00     2.7

I am trying to take an hourly average of the Value column, however I cannot seem to make this work correctly. The final output would show the starting hour for the TimeStamp, and the averaged value for the Value column.

For the final output I am looking to get a full timestamp as a result, not just the hour. So from 14:00 - 14:59 on 2005-02-17 the resulting output would be:

2005-02-17 14:00:00    2.125

Upvotes: 8

Views: 22862

Answers (5)

Teja
Teja

Reputation: 13534

SELECT DATEPART(hour,Col1) as hourcol,AVG(Col2)
FROM Yourtable
GROUP BY hourcol;

OR

SELECT SUBSTRING(Col1,1,14)+'00' AS hourcol,AVG(Col2)
FROM Yourtable
GROUP BY hourcol;

In this query DATEPART function calculates the hour value for all the values in the DATETIME column and based on each hour the average of 2nd column is calculated at hour level.

Upvotes: 4

pete
pete

Reputation: 25081

I would do it like this:

SELECT      CAST(FLOOR(CAST(timestamp AS float)) AS datetime) AS day --strip time
            , DATEPART(hh, timestamp) AS hour
            , AVG(value) AS average
FROM        times
GROUP BY    CAST(FLOOR(CAST(timestamp AS float)) AS datetime)
            , DATEPART(hh, timestamp)

Example fiddle.

Upvotes: 6

John Dewey
John Dewey

Reputation: 7093

select Time_Stamp_Hour=dateadd(hh,datepart(hh,Time_Stamp), cast(CAST(Time_Stamp as date) as datetime))
, AvgValue=AVG(Value)
from ValueLog
group by dateadd(hh,datepart(hh,Time_Stamp), cast(CAST(Time_Stamp as date) as datetime))

Result:

Time_Stamp_Hour         AvgValue
----------------------- ----------------------
2005-02-17 13:00:00.000 2.5
2005-02-17 14:00:00.000 2.125
2005-02-17 15:00:00.000 2.45
2005-02-17 16:00:00.000 2.7

Compatibility: Sql Server 2008+

Upvotes: 5

adyusuf
adyusuf

Reputation: 825

SELECT
    AVG(myvalue) [Average],
    DATEADD(HOUR, DATEPART(HOUR, mydate), CAST(CAST(mydate as Date) as datetime)) [Hour]
FROM
    myTable
GROUP BY
    DATEADD(HOUR, DATEPART(HOUR, mydate), CAST(CAST(mydate as Date) as datetime))
ORDER BY
    DATEADD(HOUR, DATEPART(HOUR, mydate), CAST(CAST(mydate as Date) as datetime))

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

I think you also want it grouped by date, not only by hour, right?

select
  convert(VARCHAR(10), date, 111) as aDate,
  datepart(HH, date) anHour,
  avg(value) anAverage
from t
group by convert(VARCHAR(10), date, 111), datepart(HH, date)

Or this:

; with aTable as (
select
  convert(VARCHAR(10), date, 111) as aDate,
  datepart(HH, date) anHour,
  value
from t)
select aDate, anHour, avg(value) from aTable
group by aDate, anHour

Upvotes: 0

Related Questions