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";"

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

StackExchange Code Review Q#82931, answer score: 3