CODEWITHSUNDEEP
mysqlsql
Gin Kevin
Gin Kevin

Reputation: 67

SQL: get avg result from last N (two) entries

I have found others with the same question. However none of the explanations worked for me and to be honest I'm not sure I understand why. So let me give you my situation:

There is a database named: enigma.
with a table consisting of three fields: number(a.i.), second(int), fourth(int)
this table has 3 entries: 1 - 100 - 200 / 2 - 200 -200 / 3 - 300 - 400
what I'm trying to achieve is getting the avarage second(int) from the last two entries.
My desired result would be: 250
The code I use is:

select AVG(second)
from (select second
  from enigma
   where (select count(*) from enigma as e
           where e.second = enigma.second) <=2 ) as t


However the result is always 200 (the avarage of all entries).
Can someone explain to me why, and possibly come up with a solution?

Kind Regards.

Upvotes: 0

Views: 66

Answers (1)

neshkeev
neshkeev

Reputation: 6476

Try this:

select avg(second)
  from (select second
          from enigma
         order by number desc 
         limit 2
       ) t

SQLFiddle

Upvotes: 3

Related Questions