CODEWITHSUNDEEP
sqlaverage
Dalton Russell
Dalton Russell

Reputation: 101

How do I average the last 5 minutes of entries from a SQL database?

I have a table that stores time, heart_rate, and player_id. I need to somehow take an average of the heart rate data over the last five minutes and group each average by the player ID.

Upvotes: 0

Views: 982

Answers (2)

JamieD77
JamieD77

Reputation: 13949

Another option is to use DATEDIFF

SELECT  AVG(heart_rate) AS heart_rate,
        player_id
FROM    tablename
WHERE   DATEDIFF(MINUTE, time, GETDATE()) < 5
GROUP BY player_id

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271231

This is partly a response to Donal's answer (and too long for a comment).

You do not want to use the following:

WHERE datepart(minute, (time - GETDATE())) < 5

There are two reasons. First, this cannot make use of an index on time. That is, the calculation is not sargable. Second, the subtraction is backwards, unless time values are in the future.

A better way to write this is:

select player_id, avg(heart_rate)
from table t
where time >= dateadd(minute, -5, getdate())
group by player_id;

Upvotes: 2

Related Questions