SQL Server - multiple counts in one query results issue

Question

I am trying to count the grades of students using a SELECT query. This is based on 3 tables:

• Student (StudentID PRIMARY KEY)
• Grade (GradeID PRIMARY KEY)
• GradeLine (GradeLineID PRIMARY KEY, StudentID FOREIGN KEY, GradeID FOREIGN KEY)

The problem: when using the below query, if one of the criteria is null (example being Student 3 has no distinctions, but has a mark in all other columns), then that Student is removed from the query, I want them to still be present and just display a 0 instead

SELECT
    GradeLine.StudentID,
    COUNT(CASE WHEN GradeLine.GradeID = 1 THEN (GradeLine.GradeID) END) AS Distinction,
    COUNT(CASE WHEN GradeLine.GradeID = 2 THEN (GradeLine.GradeID) END) AS  Merit,
    COUNT(CASE WHEN GradeLine.GradeID = 3 THEN (GradeLine.GradeID) END) AS Pass,
    COUNT(CASE WHEN GradeLine.GradeID = 4 THEN (GradeLine.GradeID) END) AS Fail,
    COUNT (GradeLine.GradeID) AS Total
FROM 
    GradeLine
GROUP BY 
    GradeLine.StudentID

Example data expected:

StudentID   Distinction Merit   Pass    Fail    Total
------------------------------------------------------
   1             1         3     4       2       10
   2             1         7     2       1       11
   3             0         3     3       5       11
   4             0        12     0       0       12
   5             6         3     0       0        9

Please could someone advise me on what I am doing wrong?

NEW current results:

StudentID   Distinction Merit   Pass    Fail    Total
-----------------------------------------------------
   1             0        0      10      0       10
   2            11        0       0      0       11
   3             0        0       0     11       11
   4             0       12       0      0       12
   5             0        0       0      9        9

Table Code, Constraints and Sample Data:

--CREATE TABLES
CREATE TABLE Student (StudentID INT IDENTITY (1,1) NOT NULL,
Studentname VARCHAR(50))

CREATE TABLE Grade (GradeID INT IDENTITY (1,1) NOT NULL,
Gradename VARCHAR(50))

CREATE TABLE GradeLine (GradeLineID INT IDENTITY (1,1) NOT NULL,
StudentID INT,
GradeID INT)

--PK CONSTRAINTS
ALTER TABLE Student ADD CONSTRAINT StudentID_PK PRIMARY KEY (StudentID)
ALTER TABLE Grade ADD CONSTRAINT GradeID_PK PRIMARY KEY (GradeID)
ALTER TABLE GradeLine ADD CONSTRAINT GradeLine_PK PRIMARY KEY (GradeLineID)

--FK CONSTRAINTS
ALTER TABLE GradeLine ADD CONSTRAINT StudentID_GL2S FOREIGN KEY (StudentID) REFERENCES Student (StudentID)
ALTER TABLE GradeLine ADD CONSTRAINT GradeID_GL2G FOREIGN KEY (GradeID) REFERENCES Grade (GradeID)

--DATA 
INSERT INTO Student VALUES ('Student A')
INSERT INTO Student VALUES ('Student B')
INSERT INTO Student VALUES ('Student C')

INSERT INTO Grade VALUES ('Distinction')
INSERT INTO Grade VALUES ('Merit')
INSERT INTO Grade VALUES ('Pass')
INSERT INTO Grade VALUES ('Fail')

--STUDENT A 
INSERT INTO GradeLine VALUES (1, 1) --STUDENT A DISTINCTION
INSERT INTO GradeLine VALUES (1, 1) --STUDENT A DISTINCTION
INSERT INTO GradeLine VALUES (1, 2) --STUDENT A MERIT

--STUDENT B
INSERT INTO GradeLine VALUES (2, 1) --STUDENT A DISTINCTION
INSERT INTO GradeLine VALUES (2, 1) --STUDENT A DISTINCTION
INSERT INTO GradeLine VALUES (2, 2) --STUDENT A MERIT
INSERT INTO GradeLine VALUES (2, 5) --STUDENT A FAIL

--STUDENT C
INSERT INTO GradeLine VALUES (3, 2) --STUDENT A MERIT
INSERT INTO GradeLine VALUES (3, 3) --STUDENT A PASS
INSERT INTO GradeLine VALUES (3, 4) --STUDENT A FAIL

Answer 1

You can make use of PIVOT :

SELECT      piv.StudentID,
            piv.[1] AS Distinction,
            piv.[2] AS Merit,
            piv.[3] AS Pass,
            piv.[4] AS Fail,
            (piv.[1] + piv.[2] + piv.[3] + piv.[4]) AS Total
FROM 
(
  SELECT    StudentID, GradeID
  FROM      GradeLine
) src
PIVOT
(
  COUNT(GradeID)
  FOR GradeID IN ([1], [2], [3], [4])
) piv;

This should work out of the box where the COUNT is 0.

You can check the entire thing in action here -> http://rextester.com/NUER1494

Hope this helps!!!