2
\$\begingroup\$

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" 
\$\endgroup\$
2
\$\begingroup\$

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.