CODEWITHSUNDEEP
sqlsql-servert-sql
Shreyash Padhi
Shreyash Padhi

Reputation: 73

Need proper output in the format below

I have a table in this format:

courseID studentID result
c1 s1 pass
c1 s2 fail
c2 s1 fail
c3 s3 pass

I want results in this format:

courseID studentID result
course total students passed students

I have tried this query

SELECT course, count(course) AS "total_students"
    , (SELECT COUNT(*) FROM table WHERE status = 'pass') AS passed
    , (SELECT COUNT(*) FROM table WHERE status = 'fail') AS failed
FROM table
GROUP BY course;

Upvotes: 0

Views: 58

Answers (2)

Subhodaya Behera
Subhodaya Behera

Reputation: 83

Try this:

select t1.c as course
    , t1.t as “total _students”
    , t2.tp as “passed_students”
    , t3.tf as “failed_students”
from
(select course as c, count(course) as t from table group by course) as t1,
(select course as c, count(course) as tp from table where status = ‘pass’ group by course) as t2,
(select course as c, count(course) as tf from table where status = ‘fail’ group by course) as t3 where t1.c=t2.c and t2.c=t3.c

Upvotes: -2

Thom A
Thom A

Reputation: 95544

Your subqueries are lacking WHERE criteria so they are just counting all the rows in the table. What you want is aggregation. Specifically conditional aggregation.

SELECT Course,
       COUNT(*) AS TotalStudents,
       COUNT(CASE result WHEN 'pass' THEN 1 END) AS Passed,
       COUNT(CASE result WHEN 'fail' THEN 1 END) AS Failed
FROM dbo.YourTable
GROUP BY Course;

Upvotes: 4

Related Questions