Hi smile,
Refer below sample query.
SQL
DECLARE @Test AS TABLE(Session VARCHAR(20), Term VARCHAR(10), Exam VARCHAR(10), AdmissionNo VARCHAR(10), Subject VARCHAR(10), Max INT, Mark INT)
INSERT INTO @Test VALUES('S-19','Term-1','A1-T1','R-01','English',40,23)
INSERT INTO @Test VALUES('S-19','Term-1','A2-T1','R-01','English',60,48)
INSERT INTO @Test VALUES('S-19','Term-1','A1-T1','R-02','English',40,33)
INSERT INTO @Test VALUES('S-19','Term-1','A2-T1','R-02','English',60,58)
INSERT INTO @Test VALUES('S-19','Term-2','A1-T2','R-01','English',40,33)
INSERT INTO @Test VALUES('S-19','Term-2','A2-T2','R-01','English',60,53)
INSERT INTO @Test VALUES('S-19','Term-2','A1-T2','R-02','English',40,35)
INSERT INTO @Test VALUES('S-19','Term-2','A2-T2','R-02','English',60,45)
SELECT Session,
Subject,
MIN(Obt)'MinMark',
MAX(Obt) 'MaxMark',
ROUND(CAST(MIN(Percentage) AS DECIMAL(10,2)),0) 'MinPercentage',
ROUND(CAST(MAX(Percentage) AS DECIMAL(10,2)),0) 'MaxPercentage',
(CASE WHEN Round((SUM(Obt)) * 100/ SUM(Total),1) >= 90 THEN 'A+'
WHEN Round((SUM(Obt)) * 100/ SUM(Total),1) >= 80 THEN 'A'
WHEN Round((SUM(Obt)) * 100/ SUM(Total),1) >= 70 THEN 'B'
WHEN Round((SUM(Obt)) * 100/ SUM(Total),1) >= 60 THEN 'C'
WHEN Round((SUM(Obt)) * 100/ SUM(Total),1) >= 50 THEN 'D'
WHEN Round((SUM(Obt)) * 100/ SUM(Total),1) >= 40 THEN 'E'
ELSE 'FAIL' END ) AS Grade
FROM(
SELECT Session,
AdmissionNo,
Subject,
SUM(Max) 'Total',
SUM(Mark) 'Obt',
SUM(Mark)*CAST(100 AS DECIMAL(10,2))/SUM(Max) 'Percentage'
FROM @Test WHERE Session='S-19'
GROUP BY Session,Subject,AdmissionNo) t
GROUP BY Session,Subject
Output
| Session |
Subject |
MinMark |
MaxMark |
MinPercentage |
MaxPercentage |
Grade |
| S-19 |
English |
157 |
171 |
79 |
86 |
A |
