Database

JammoD87 wrote:

I was asked a question the other day

-- TODO: Change these as required:
DECLARE @DistinctionThreshold int = 90;
DECLARE @PassThreshold int = 70;

WITH cteCounts As
(
    SELECT
        ClassId,
         SUM(CASE 
            WHEN Marks < @PassThreshold THEN 1 
            ELSE 0 
        END) As Failed,
        SUM(CASE 
            WHEN Marks >= @PassThreshold And Marks < @DistinctionThreshold THEN 1 
            ELSE 0 
        END) As Passed,
        SUM(CASE 
            WHEN Marks >= @DistinctionThreshold THEN 1 
            ELSE 0 
        END) As Distinction
    FROM
        dbo.Marks
    GROUP BY
        ClassId
)
SELECT
    C.Class,
    M.Failed,
    M.Passed,
    M.Distinction
FROM
    dbo.Class As C
    INNER JOIN cteCounts As M
    ON M.ClassId = C.ClassId
;

CREATE TABLE Class
(
    Id   BIGINT PRIMARY KEY
   ,Name VARCHAR(50)
)
INSERT INTO Class (Id, Name) VALUES (1, '7th Class')
INSERT INTO Class (Id, Name) VALUES (2, '8th Class')
INSERT INTO Class (Id, Name) VALUES (4, '10th Class')
CREATE TABLE Marks
(
    StudentId BIGINT,
    ClassId BIGINT,
    Marks BIGINT
)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (1, 1, 35)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (2, 1, 90)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (3, 2, 75)
INSERT INTO Marks (StudentId, ClassId, Marks) VALUES (4, 2, 30)

SELECT c.Name,
      (SELECT COUNT( * ) FROM Marks WHERE ClassId = c.Id AND Marks >= 50) AS [Passed],
      (SELECT COUNT( * ) FROM Marks WHERE ClassId = c.Id AND Marks < 50) AS [Failed]
  FROM Class c

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]