tmccrank
asked on
Question about COUNT in sub-query
Hello,
I have a number of Modules, each of which has a number of multiple choice questions.
I need a query that will return A) The user's score, B)The Module name, C) The total number of questions in each Module.
The following query returns A & B successfully, but C returns the total number of questions of ALL Modules rather that in EACH Module.
SELECT sc.Score, sc.UserNameID, m.ModuleID, m.ModuleName, un.UserName,
(SELECT COUNT(ModuleID) FROM Questions) AS QuestionCount
FROM ScoreByModule sc INNER JOIN Modules m
ON sc.ModuleID = m.ModuleID
INNER JOIN UserName un
ON sc.UserNameID = un.UserNameID
WHERE un.UserName ='JoeSmith'
GROUP BY m.ModuleName, sc.Score, sc.UserNameID, m.ModuleID, un.UserName
Thanks-
I have a number of Modules, each of which has a number of multiple choice questions.
I need a query that will return A) The user's score, B)The Module name, C) The total number of questions in each Module.
The following query returns A & B successfully, but C returns the total number of questions of ALL Modules rather that in EACH Module.
SELECT sc.Score, sc.UserNameID, m.ModuleID, m.ModuleName, un.UserName,
(SELECT COUNT(ModuleID) FROM Questions) AS QuestionCount
FROM ScoreByModule sc INNER JOIN Modules m
ON sc.ModuleID = m.ModuleID
INNER JOIN UserName un
ON sc.UserNameID = un.UserNameID
WHERE un.UserName ='JoeSmith'
GROUP BY m.ModuleName, sc.Score, sc.UserNameID, m.ModuleID, un.UserName
Thanks-
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER