SQL to retrieve aggregated data with computed columns

Question

I have a table that looks like this for about ~30 students:

StudentID    Course*      CourseStatus
1            Math         Pass
1            English      Fail
1            Science      Pass
2            Math         Fail
2            English      Pass
2            Science      Fail
etc.

*In my actual database the 'Course' column is a CourseID e.g. (1 = Math; 2 = English etc.) which references a 'CourseName' table. I amended the table above just to make it clear the nature of the problem.

I want to write a query (stored procedure) in SQL that summarises performance for a given course and returns the following:

EXEC usp_GetCourseSummary 'Math'

Total Students     Total Pass    % Pass    Total Fail    % Fail
25                 15            60        10            40

Have been scratching my head on this one for some time. Any ideas?

Answer 1

Try this:

CREATE PROCEDURE usp_GetCourseSummary
    @Course nvarchar(100)
AS     
    SET NOCOUNT ON;
    SELECT
        COUNT(StudentId) AS [Total Students],
        SUM(CASE WHEN CourseStatus = 'Pass' THEN 1 ELSE 0 END) AS [Total Pass],
        SUM(CASE WHEN CourseStatus = 'Pass' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS [% Pass],
        SUM(CASE WHEN CourseStatus = 'Fail' THEN 1 ELSE 0 END) AS [Total Fail], 
        SUM(CASE WHEN CourseStatus = 'Fail' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS [% Fail]
    FROM table1
    WHERE Course = @Course;
GO

EXEC usp_GetCourseSummary 'Math'

Result:

Total Students  Total Pass  % Pass  Total Fail  % Fail
2               1           50      1           50

Test data:

CREATE TABLE Table1 (StudentID INT NOT NULL, Course NVARCHAR(100) NOT NULL, CourseStatus NVARCHAR(100) NOT NULL);
INSERT INTO Table1 (StudentID, Course, CourseStatus) VALUES
(1, 'Math', 'Pass'),
(1, 'English', 'Fail'),
(1, 'Science', 'Pass'),
(2, 'Math', 'Fail'),
(2, 'English', 'Pass'),
(2, 'Science', 'Fail');