CODEWITHSUNDEEP
mysql
user1067018
user1067018

Reputation: 47

MySQL Query for Average Grade of last 2 attempts

I have a table:

quiz userid  attempt grade

1      3        1     33

1      3        2     67

1      3        3     90

1      3        4     20

Now, I want the last two attempts i.e., 4 and 3 and I want average grade of these 2 grades i.e, 90 and 20 Could anyone help me?

Upvotes: 0

Views: 115

Answers (2)

Nicola Cossu
Nicola Cossu

Reputation: 56407

Use ORDER and LIMIT to get the 2 last attempts and the AVG aggregation function :

 SELECT AVG(grade) AS average FROM (
   SELECT grade FROM table 
   WHERE userid = 3
   ORDER BY attempt DESC LIMIT 2) AS t

Upvotes: 1

SunHunter
SunHunter

Reputation: 11

If you want to list both test results separately, with the average in each row, then something like this maybe (otherwise you just need the subquery for the average of the two tests): 

SELECT userid, attempt, grade, 

( SELECT AVG(grade)
    FROM table
      ORDER BY attempt DESC LIMIT 0, 2 ) AS avg_grade

FROM table
ORDER BY attempt DESC LIMIT 0, 2;

Upvotes: 0

Related Questions