Hi smile,
Refer below query.
DECLARE @tblSetMarks AS TABLE(AdmissionNo VARCHAR(10),SubjectName VARCHAR(10),Mark INT, [Max] INT)
INSERT INTO @tblSetMarks VALUES('R-1','Eng',23,50)
INSERT INTO @tblSetMarks VALUES('R-2','Eng',21,50)
INSERT INTO @tblSetMarks VALUES('R-3','Eng',12,50)
INSERT INTO @tblSetMarks VALUES('R-4','Eng',29,50)
INSERT INTO @tblSetMarks VALUES('R-1','Urdu',20,50)
INSERT INTO @tblSetMarks VALUES('R-2','Urdu',25,50)
INSERT INTO @tblSetMarks VALUES('R-3','Urdu',15,50)
INSERT INTO @tblSetMarks VALUES('R-4','Urdu',30,50)
INSERT INTO @tblSetMarks VALUES('R-1','Math',22,50)
INSERT INTO @tblSetMarks VALUES('R-2','Math',27,50)
INSERT INTO @tblSetMarks VALUES('R-3','Math',19,50)
INSERT INTO @tblSetMarks VALUES('R-4','Math',45,50)
SELECT SubjectName
,MIN(Mark) AS MinMark
,MAX(Mark) AS MaxMark
,MIN(ROUND((Mark) * 100/ ([Max]),1)) AS Lowest
,MAX(ROUND((Mark) * 100/ ([Max]),1)) AS Highest
,(CASE WHEN MAX(ROUND((Mark) * 100/ ([Max]),1)) >= 90 THEN 'A'
WHEN MAX(ROUND((Mark) * 100/ ([Max]),1)) >= 80 THEN 'B'
WHEN MAX(ROUND((Mark) * 100/ ([Max]),1)) >= 70 THEN 'C'
WHEN MAX(ROUND((Mark) * 100/ ([Max]),1)) >= 60 THEN 'D'
ELSE 'Fail' END ) AS Grade
FROM @tblSetMarks
GROUP BY SubjectName
