CODEWITHSUNDEEP
mysql
user1067018
user1067018

Reputation: 47

MySQL Query for Average of last 2 attempts

I have a table:

quiz userid  attempt grade

1      3        1     33

2      3        1     67

1      3        2     90

10     3        4     20

2      3        2     67

1      3        3     55

Now For first quiz, an user gave 3attempts i.e., (33, 90, 55), Now I need the average for last 2 attempts ( (90 + 55)/2) for that scormid and userid too

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 Need the OP like

userid quiz No.of Attempts Grade 

3      1         3           (90+55)/2 i.e., 72.5
3      2         2            (67+67)/2 i.e., 67
3      10        1               20

Upvotes: 1

Views: 324

Answers (5)

Vapire
Vapire

Reputation: 4588

Try this (assuming your table is called 'grades')

SELECT last_two_grades.userid, last_two_grades.quiz, AVG(last_two_grades.grade) AS average
  FROM (
        SELECT grades.userid, grades.quiz, grades.grade
          FROM grades
      ORDER BY grades.attempt DESC
         LIMIT 2
       ) AS last_two_grades
GROUP BY last_two_grades.userid, last_two_grades.quiz

With that you can select the average of the last two grades on a per user basis. If you want to select it for a specific user you can do so by adding

HAVING last_two_grades.userid = 3

to the end of the query and for a certain quiz you can add

HAVING last_two_grades.quiz = 1

and to get the average grades for a user at a certain quiz you must specify

HAVING last_two_grades.quiz = 1 AND last_two_grades.userid = 3

Upvotes: 0

xQbert
xQbert

Reputation: 35353

Makes 2 assumption: that all you attempts are sequential and have no voids (Ie 1,2,3,4) exists and not 1,3,4,6) IF the latter, I can correct. (will use a limit/order descending)

assumes you want the avg of the 2 grades per person/per quiz.

Explanation: Executes a sub query to : get quiz, user where attempt is equal to max attempt for the same quiz/user less 1. Performance isn't ideal there is likely a faster way but this shold work.

SELECT T1.quiz, T1.userID, avg(T1.grade)
FROM TABLE T1
   T1.Attempt >= 
    (Select max(T3.attempt) -1
     from table T3
     where T3.QUIZ=T1.Quiz 
     AND T3.UserID=T1.UserID)
GROUP BY T1.Quiz, T1.UserID

Upvotes: 0

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29985

Selecting the last two rows = selecting the first two rows of a reversed set.

Simply order by attempt DESC (that makes it 4,3,2,1) and then grab the first two (4,3).

SELECT * FROM table WHERE <...> ORDER BY attempt DESC LIMIT 2

Upvotes: 1

Rich Adams
Rich Adams

Reputation: 26584

First you need to select just the two rows with highest attempts, which is what the subquery does, then average the grades from the derived table.

SELECT 
    AVG(grade) 
FROM (
      SELECT 
          grade 
      FROM 
          `table`
      ORDER BY attempt DESC 
      LIMIT 2
     ) t;

This will give you a result of 55.0000, which is the average of 90 and 20, the grades for the last two attempts.

Upvotes: 6

Michiel van Vaardegem
Michiel van Vaardegem

Reputation: 2035

SELECT AVG(grade), attempt
FROM table
GROUP BY attempt
ORDER BY attempt DESC
LIMIT 2

Upvotes: 1

Related Questions