Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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
0
nmarano
Asked:
nmarano
  • 6
  • 6
1 Solution
 
_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
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
_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
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now