I am trying to count the grades of students using a SELECT query. This is based on 3 tables:
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
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!!!
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments