Combine Mysql limit query

Question

Is it possible to combine these queries into one query? I am trying to see and average all scores but also count and average just the last three based on the same grouping.

This is the main query:

$query4 ="SELECT 
studentsisid,coursename,learningoutcomeid,learningoutcomename,count(RecordID) as assessmentcount, 
count(if(outcomescore >= 3,outcomescore, NULL)) as over3,
avg(outcomescore) as scoreavg
from studentscores 
WHERE studentsisid LIKE '$studentid' AND coursename='$coursename'
Group by studentsisid,coursename, learningoutcomeid 
ORDER BY studentname, learningoutcomename";

This is an example of the current sample data based on the query above is below (loid is learningoutcomeid and loname is learningoutcomename)

studentsisid;coursename;loid;loname;assessmentcount‌;over3;scoreavg
1234;"Course1";"7982";"LearningOutcome1";"1";"1";"3.2" 
1234;"Course1";"7995";"LearningOutcome2";"4";"4";"2.5" 
1234;"Course1";"7991";"LearningOutcome3";"6";"6";"3.8" 
1234;"Course1";"7889";"LearningOutcome4";"1";"1";"3.4" 
1234;"Course1";"7839";"LearningOutcome5";"2";"2";"2,6"

The goal is to get something like this where recent over 3 only looks at the last three records for each learningoutcomeid for each student for each course.

studentsisid;coursename;loid;loname;assessmentcount‌;over3;scoreavg;recentover3;recenscoretavg
1234";"Course1";"7982";"LearningOutcome1";"1";"1";"3.2";"1";"3.2" 
1234;"Course1";"7995";"LearningOutcome2";"4";"4";"2.5";"2";"2.5" 
1234;"Course1";"7991";"LearningOutcome3";"6";"6";"3.8";"3";"3.2" 
1234;"Course1";"7889";"LearningOutcome4";"1";"1";"3.4";"1";"3.2"
1234;"Course1";"7839";"LearningOutcome5";"2";"2";"2,6";"1";"3.2"

This current solution is close but it is only getting the first learningoutcomeid and not the others that match for a particular student for a particular course.

studentsisid;coursename;loid;loname;assessmentcount‌;over3;scoreavg;recentover3;recentscoreavg
1234";"Course1";"7982";"LearningOutcome1";"1";"1";"3.2";"1";"3.2" 

• 200_success: What you may and may not do after receiving answers. I've rolled back Rev 10 → 8. I see that you have asked a follow-up question, which is the right thing to do.

Answer 1

You need to convert your second query to a subquery and join it with the main query. To pass the learning outcome into the inner query you need to use the user-defined variable:

SELECT 
 sc.studentsisid,
 sc.coursename,
 sc.learningoutcomeid,
 sc.learningoutcomename,
 count(sc.RecordID) as assessmentcount, 
 count(if(sc.outcomescore >= 3,sc.outcomescore, NULL)) as over3,
 avg(sc.outcomescore) as scoreavg,
 l.recentover3,
 l.recentscoreavg 
FROM studentscores sc
INNER JOIN (
 SELECT studentsisid,
 learningoutcomeid,
 count(if(outcomescore >= 3,outcomescore, NULL)) as recentover3, 
 avg(outcomescore) as recentscoreavg 
 FROM (
 SELECT 
 (@studentsisid := studentsisid) as studentsisid,
 (@coursename := coursename) as courcename,
 (@learningoutcomeid := learningoutcomeid) as learningoutcomeid,
 @num := if(@studentsisid = studentsisid and 
 @coursename = coursename and 
 @learningoutcomeid = learningoutcomeid, 
 @num + 1, 1) as row_number,
 outcomescore
 FROM studentscores 
 ORDER BY studentsisid, coursename, learningoutcomeid, recordid DESC) as r
 where row_number <= 3) AS l
ON sc.studentsisid = l.studentsisid AND sc.learningoutcomeid = l.learningoutcomeid
WHERE sc.studentsisid LIKE '$studentid' AND sc.coursename='$coursename'
GROUP BY sc.studentsisid, sc.coursename, sc.learningoutcomeid 
ORDER BY sc.studentname, sc.learningoutcomename

• Kevin: Thanks cha! This definitely works but then after I ran it, I realize that my logic was completely off and I am not getting the results I was looking to get. I am trying to get the overall average and the average for the last three submissions but the second query would limit to three grouped studentid and not limit to the last 3 scores. I will give credit for you help because you solved the problem as I wrote it.
• cha: The solution gives the average of the last three scores. Do you just want to see these three scores repeating in each rows instead?
• Kevin: I found a solution that works but had to use a nested while statement. The nested while statement is pretty slow so you may be able to help here. I have a question on that at:codereview.stackexchange.com/questions/83134/…
• cha: try that one. Can you please create a SQLFiddle with your example? I can't create it as your data is not formatted as per the fiddle liking
• Kevin: I will try this and try to test SQLFiddle. Thanks for all your help!