current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 1 down vote favorite

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"
share|improve this question
    
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. –  200_success♦ Mar 4 at 7:46

1 Answer 1

up vote 0 down vote accepted

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
share|improve this answer
    
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. –  Kevin Mar 2 at 22:56
    
You can do this as well. Do you want me to show how? –  cha Mar 2 at 23:31
    
That would be great. I am definitely having trouble trying to figure it out. –  Kevin Mar 2 at 23:32
    
check out the updated answer –  cha Mar 2 at 23:40
    
Thanks! I will give it a try and let you know how it works out. –  Kevin Mar 2 at 23:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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