Database grouping of rows using values into columns

indian143

Hi All, I have a table as Marks, depending upon their marks they got I have to count how many of them got failed, passed and distinction and another table is Class which specifies the class. Class table has values like Can anybody please help me in achieving this structure in SQL Server?

Id | Class

1 | 7th Class
2 | 8th Class
4 | 10th Class

Marks Table has values as

StudentId | ClassId | Marks |
1 | 1 | 35
2 | 1 | 90
3 | 2 | 75
4 | 2 | 30
5 | 4 | 99
6 | 4 | 25
7 | 4 | 36
8 | 4 | 70
9 | 4 | 65
10 | 4 | 55

Now the resulting table should look as below

Class | Failed | Passed |Distinction
7 th Class count count count

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Richard Deeming

You need to define what counts as a fail, a pass, and a distinction. You also need to specify which DBMS you're using. Assuming a recent version of Microsoft SQL Server, something like this should work:

-- 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
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Lost User

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)

Try something like

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

If you try the query here[^], you'll not just see the actual result, but also get timings and a nice execution plan.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)