January 9, 2004 at 3:30 pm
Hello,
I am having trouble with a query that needs to perform SUM and COUNT on the same field.
The code is as follows:
**********************************
SELECT TOP 100 PERCENT
SD.Permnum,
TT.TestShortName,
"TestScore" = SUM(CAST(TS.Score AS INT))/COUNT(TS.Score)
FROM
Teacher_Data_Main TD
INNER JOIN Student_Data_Main SD ON TD.TeacherID=SD.TeacherID
INNER JOIN tblRC_StandardsTestScores03_04 TS ON TS.Permnum=SD.Permnum
INNER JOIN tblTests TT ON TT.TestShortName = TS.TestShortName
WHERE
TD.FirstName = 'Becky'
AND
TD.LastName = 'Clark'
GROUP BY SD.Permnum, TT.TestShortName
ORDER BY SD.Permnum
**********************************
'Score' is a BIT type field. 1 = correct answer, 0 = incorrect answer. The SUM of the Score field gives me the number of correct answers, and the COUNT of the Score field gives me the number of questions. I need for the calculation at "TestScore" to reflect the percentage correct.
I've tried a number of things, Including making this query an Inline select statement where the inline select does the COUNT, and the outer select does the SUM, but this did not work either.
Any ideas?
Thanks for your help!
CSDunn
January 9, 2004 at 3:34 pm
Never mind, I got it:
"TestScore" = 100 * SUM(CAST(TS.Score AS INT))/COUNT(TS.Score)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply