Check the below query.
DECLARE @TestDemo AS TABLE(AdmissionNo VARCHAR(10),Subjects VARCHAR(10),Total INT, Obtain INT)
INSERT INTO @TestDemo VALUES('R-01','English',50,23)
INSERT INTO @TestDemo VALUES('R-01','Urdu',50,5)
INSERT INTO @TestDemo VALUES('R-01','Math',50,25)
INSERT INTO @TestDemo VALUES('R-01','Science',50,3)
INSERT INTO @TestDemo VALUES('R-02','English',50,7)
INSERT INTO @TestDemo VALUES('R-02','Urdu',50,27)
INSERT INTO @TestDemo VALUES('R-02','Math',50,3)
INSERT INTO @TestDemo VALUES('R-02','Science',50,5)
INSERT INTO @TestDemo VALUES('R-03','English',50,30)
INSERT INTO @TestDemo VALUES('R-03','Urdu',50,30)
INSERT INTO @TestDemo VALUES('R-03','Math',50,30)
INSERT INTO @TestDemo VALUES('R-03','Science',50,30)
SELECT AdmissionNo,SUM(Passed) Passed,SUM(Failed) Failed,(SUM(Passed)+SUM(Failed)) Total, SUM(Total) Max, SUM(Obtain) Mark
FROM (SELECT AdmissionNo,Total,Obtain
,(CASE WHEN Round((Obtain) * 100/ Total,1) >=33 THEN 1 ELSE 0 END) AS [Passed]
,(CASE WHEN Round((Obtain) * 100/ Total,1) < 33 THEN 1 ELSE 0 END) AS [Failed]
FROM @TestDemo)t
GROUP BY AdmissionNo
Output
| AdmissionNo |
Passed |
Failed |
Total |
Max |
Mark |
| R-01 |
2 |
2 |
4 |
200 |
56 |
| R-02 |
1 |
3 |
4 |
200 |
42 |
| R-03 |
4 |
0 |
4 |
200 |
120 |
