Combine Mysql limit query

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"  1 Answer 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

• 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. Mar 2 '15 at 22:56
• The solution gives the average of the last three scores. Do you just want to see these three scores repeating in each rows instead?
– cha
Mar 3 '15 at 22:11
• 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/… Mar 3 '15 at 22:46
• 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
– cha
Mar 4 '15 at 0:02
• I will try this and try to test SQLFiddle. Thanks for all your help! Mar 4 '15 at 13:47