CODEWITHSUNDEEP
sqlsql-server
user1679941
user1679941

Reputation:

How can I show a count value in SQL instead of listing out many rows?

I have three tables: Exam, Objective and Test.

I created this SQL to report on the data:

SELECT name, objectiveId, TestId
FROM exam
LEFT JOIN Test ON Test.ExamId = Exam.ExamId
LEFT JOIN Objective ON Objective.ExamId = Exam.ExamId

What I would like to do is instead of showing the objectiveId and TestId then I would like to show a count of how many objectives or tests there are for each exam. Something like:

Exam    Objectives   Tests
Exam1            1      10
Exam2            2       0

Upvotes: 0

Views: 48

Answers (1)

Joseph B
Joseph B

Reputation: 5669

You could use:

SELECT name Exam, COALESCE(COUNT(objectiveId), 0) Objectives, COALESCE(COUNT(TestId), 0) Tests
FROM exam
LEFT JOIN Test ON Test.ExamId = Exam.ExamId
LEFT JOIN Objective ON Objective.ExamId = Exam.ExamId
GROUP BY name;

Upvotes: 1

Related Questions