Reputation: 47
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
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
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
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
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
Reputation: 2035
SELECT AVG(grade), attempt
FROM table
GROUP BY attempt
ORDER BY attempt DESC
LIMIT 2
Upvotes: 1