CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rohit Chaudhari
Rohit Chaudhari

Reputation: 757

Count from Same column but different conditions

I am getting three different results in the following queries, they all have same joins & column to count just a condition to count is different, is there any way so that I could get the Questions, Correct, Attempted & IeslTopics.Name columns together in a single query?

SELECT     COUNT(TestResults2.QuestionID) AS Questions, Topics.Name
FROM         TestResults 
INNER JOIN   TestResults2 ON TestResults.ID = TestResults2.TestResultID 
INNER JOIN   QuestionBank ON TestResults2.QuestionID = QuestionBank.ID 
INNER JOIN   Topics ON QuestionBank.TopicID = Topics.ID
WHERE        (TestResults.StudentID = 1) AND (TestResults.ID = 46)
GROUP BY   Topics.Name
//All topic

SELECT     COUNT(TestResults2.QuestionID) AS Correct, Topics.Name
FROM         TestResults 
INNER JOIN   TestResults2 ON TestResults.ID = TestResults2.TestResultID 
INNER JOIN   QuestionBank ON TestResults2.QuestionID = QuestionBank.ID AND 
                             TestResults2.Answer = QuestionBank.Answer 
INNER JOIN   Topics ON QuestionBank.TopicID = Topics.ID
WHERE        (TestResults.StudentID = 1) AND (TestResults.ID = 46)
GROUP BY   Topics.Name
//Correct topic

SELECT     COUNT(TestResults2.QuestionID) AS Attempted, Topics.Name
FROM         TestResults 
INNER JOIN   TestResults2 ON TestResults.ID = TestResults2.TestResultID 
INNER JOIN   QuestionBank ON TestResults2.QuestionID = QuestionBank.ID AND 
                             TestResults2.Answer <> '\0' 
INNER JOIN   Topics ON QuestionBank.TopicID = Topics.ID
WHERE        (TestResults.StudentID = 1) AND (TestResults.ID = 46)
GROUP BY   Topics.Name
//Attempted topic

Upvotes: 3

Views: 2761

Answers (2)

TheJoeIaut
TheJoeIaut

Reputation: 1532

Join the 3 queries and also join it to a query where you select all the topic names.

//edit something like this:

Select t.Name, a.Questions, b.Correct,c.Attempted from Topics t Left Join (
SELECT     COUNT(TestResults2.QuestionID) AS Questions, Topics.Name
FROM         TestResults INNER JOIN
                      TestResults2 ON TestResults.ID = TestResults2.TestResultID INNER JOIN
                      QuestionBank ON TestResults2.QuestionID = QuestionBank.ID INNER JOIN
                      Topics ON QuestionBank.TopicID = Topics.ID
WHERE     (TestResults.StudentID = 1) AND (TestResults.ID = 46)
GROUP BY Topics.Name) a On t.Name=a.Name
Inner Join(
SELECT     COUNT(TestResults2.QuestionID) AS Correct, Topics.Name
FROM         TestResults INNER JOIN
                      TestResults2 ON TestResults.ID = TestResults2.TestResultID INNER JOIN
                      QuestionBank ON TestResults2.QuestionID = QuestionBank.ID AND 
                      TestResults2.Answer = QuestionBank.Answer INNER JOIN
                      Topics ON QuestionBank.TopicID = Topics.ID
WHERE     (TestResults.StudentID = 1) AND (TestResults.ID = 46)
GROUP BY Topics.Name) b On t.Name=b.Name
Inner Join(
SELECT     COUNT(TestResults2.QuestionID) AS Attempted, Topics.Name
FROM         TestResults INNER JOIN
                      TestResults2 ON TestResults.ID = TestResults2.TestResultID INNER JOIN
                      QuestionBank ON TestResults2.QuestionID = QuestionBank.ID AND 
                      TestResults2.Answer <> '\0' INNER JOIN
                      Topics ON QuestionBank.TopicID = Topics.ID
WHERE     (TestResults.StudentID = 1) AND (TestResults.ID = 46)
GROUP BY Topics.Name) c on c.Name=t.Name

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

You can use a conditional inside a SUM to supply ones for items that you are looking for and zeros for other items, like this:

SELECT
    COUNT(TestResults2.QuestionID) AS Questions
,   SUM(CASE WHEN TestResults2.Answer = QuestionBank.Answer THEN 1 ELSE 0 END) AS Correct
,   SUM(CASE WHEN TestResults2.Answer <> '\0' THEN 1 ELSE 0 END) AS Attempted
,   Topics.Name
FROM         TestResults INNER JOIN
                  TestResults2 ON TestResults.ID = TestResults2.TestResultID INNER JOIN
                  QuestionBank ON TestResults2.QuestionID = QuestionBank.ID INNER JOIN
                  Topics ON QuestionBank.TopicID = Topics.ID
WHERE     (TestResults.StudentID = 1) AND (TestResults.ID = 46)
GROUP BY Topics.Name

Upvotes: 3

Related Questions