Looping through results?

Experts-

I have a table that holds answers and another table that holds student responses.  The answer table can have multiple 'M' answers for an item(question).  I'm trying to figure out how many 'M' responses each student got for a particular question.  

<!--- This will pull all answers for this test --->
<cfquery name="getAnswers" datasource="#request.datasource#">
      SELECT itemID, answer, answerID
    FROM answers a
    INNER JOIN itemtoItemset i on i.item = a.itemID
    WHERE i.itemsetID = 60
    AND a.answerID ='M'
</cfquery>

<!--- This will pull all students who took this test --->
<cfquery name="getStudentResponse" datasource="#request.datasource#">
      SELECT *
   FROM studentresponse
    WHERE itemsetID = 60
    ORDER BY  studentID
</cfquery>

Can someone tell me the best way to get a count for each individual student?  Initially I had written a query that had the count function, but with the multiple 'M' responses for an individual ItemID(question) it was throwing my counts off.

Any help would be appreciated

Thanks
nick
LVL 2
nmaranoAsked:
Who is Participating?
 
_agx_Commented:
Oh, I didn't know the student table had that column too.  (That seems a little odd ?) I'd probably add it to the join instead.  It should produce the same results

SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalNum
FROM   studentresponse sr
              INNER JOIN answers a ON a.ItemID = sr.ItemID AND a.answer = sr.Response
              INNER JOIN itemtoItemset i on i.item = a.itemID AND i.itemsetID  = sr.itemsetID  
WHERE sr.itemsetID = 60
AND a.answerID ='M'
GROUP BY sr.StudentID
0
 
_agx_Commented:
> The answer table can have multiple 'M' answers for an item(question)

I don't quite understand that.  It should be a simple COUNT.  

Can you post a small sample of the data, and what counts you expect? Also what's your db again?




0
 
gdemariaCommented:
Right, more information please...

it seems you're looking for a count of answers (of type "M") for each question.. .but I dont' see the questions table in your queries...

So, it's a count of M-type answers for each question for each student.. .so it would be like this...

Steve
 Question A - 10
 Question B - 7
Bob
  Question A - 12
  Question B - 4

where the number is the count of questions that have type M

Please let us know...
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
nmaranoAuthor Commented:
agx-

So in my answer table I have....

itemId      answer         answerID
882                E                  C
882                A                   M
882                B                   M
882                C                   m
882                D                   M
883                A                    C
883                B                     M
883                D                    M
883                E                     M
884                A                    C
884               B                      M
885              B                       C
886              C                      M
886              D                        C
Studentresponse table

sudentId response     itemID
21               D                   882
21              E                   883
21             A                     884
21               B                  885
21                C                886

So based on this, studentID 21 should have a count of 3 for itemID 882,883,886

Using MYSQL
0
 
nmaranoAuthor Commented:
Hi Maria-

So as my example of data above, there will be more than one student that I will be running the queries through the get the count.  Maybe I'm just over thinking this and it's much simpler than I am making it out to be?
0
 
_agx_Commented:
Untested, but something like this should give you the "M" count for all students with at least 1 matching answer in the desired set

SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalNum
FROM   studentresponse sr
              INNER JOIN answers a ON a.ItemID = sr.ItemID
              INNER JOIN itemtoItemset i on i.item = a.itemID
WHERE i.itemsetID = 60
AND a.answerID ='M'
GROUP BY sr.StudentID, sr.ItemID
0
 
nmaranoAuthor Commented:
agx- Thanks I'll try it out

Nick
0
 
_agx_Commented:
Correction.  I think you need to join on the "response/answer" column too


SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalNum
FROM   studentresponse sr
              INNER JOIN answers a ON a.ItemID = sr.ItemID AND a.answer = sr.Response
              INNER JOIN itemtoItemset i on i.item = a.itemID
WHERE i.itemsetID = 60
AND a.answerID ='M'
GROUP BY sr.StudentID, sr.ItemID
0
 
nmaranoAuthor Commented:
So I added And where sr.itemsetID = 60 and removed sr.itemID in Group BY so now I have this...

SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalNum
FROM   studentresponse sr
              INNER JOIN answers a ON a.ItemID = sr.ItemID AND a.answer = sr.Response
              INNER JOIN itemtoItemset i on i.item = a.itemID
WHERE i.itemsetID = 60
AND sr.itemsetID = 60
AND a.answerID ='M'
GROUP BY sr.StudentID

Which I think is what I am looking for, but will do a tad more testing and let you know in a bit...

Thanks for the help

Nick
0
 
nmaranoAuthor Commented:
Agx-

Thanks!  I was over thinking this.  Thanks for the help

Nick
0
 
_agx_Commented:
> and removed sr.itemID in Group BY

Just curious - why?  Were you getting different results? If not, I'd leave it in as a good practice.  Most db's wouldn't let you exclude it anyway.
0
 
nmaranoAuthor Commented:
agx- removed it because it was not given me the results that I was expecting.  Also, removed it and because I didn't necessarily need it group by itemID as much as i needed it by StudentID.

Thanks again for you help
0
 
_agx_Commented:
Ah, ok. If you don't really need the results grouped by "itemID" you might try dropping it from the SELECT list. See if you get the same results. Just a thought.

-Later :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.