CODEWITHSUNDEEP
mysql
Dom C
Dom C

Reputation: 13

MYSQL Average N rows

Hi I have a MYSQL DB with the following data ID, TEMP, log_time

Each record has a unique value of ID, log_time is generated automatically with CURRENT_TIMESTAMP. TEMP is a float that is inserted example 29.90

I can select the most recent data using 

SELECT ID, TEMP, log_time FROM log_data 
WHERE ID = (select max(ID) from log_data)

How can I average the most recent 60 values for TEMP?

Upvotes: 1

Views: 225

Answers (2)

Ullas
Ullas

Reputation: 11556

You have to sort the top 60 rows by log_time in descending order. And then find the average of TEMP.

Query

select avg(t.TEMP) as average_temp from
(
    select ID, TEMP, log_time
    from log_data
    order by log_time desc limit 60
)t;

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try this query 

select avg(new.TEMP) from
(
    select *
    from table
    order by log_time desc limit 60
) new

select 60 records with limit 60 for latest record use order by desc

Upvotes: 0

Related Questions