Query to calculate Percentage Grade in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

I have a following data 

SessionName

Term

Exam

AdmissionNo

Subject

Max

Mark

S-19

Term-1

A1-T1

R-01

English

40

23

S-19

Term-1

A2-T1

R-01

English

60

48 =        71

S-19

Term-1

A1-T1

R-02

English

40

33

S-19

Term-1

A2-T1

R-02

English

60

58 =        91

S-19

Term-2

A1-T2

R-01

English

40

33

S-19

Term-2

A2-T2

R-01

English

60

53 =        86

S-19

Term-2

A1-T2

R-02

English

40

35

S-19

Term-2

A2-T2

R-02

English

60

45       =  80

i wrote the following query

select Session,Subject,sum(Max) 'Total',sum(Mark) 'Obt' 
from Test where Session='S-19'
group by Session,Subject,AdmissionNo

select Session,Term,Subject,AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obt' from Test where Term='Term-1'
group by Session,Term,Subject,AdmissionNo

it is showing me correct output

but i want to diaplay result like this

SessionName

Subject

MinMark

MaxMark

Lowest %

Highest %

Grade

S-19

Englsih

71

91

71

91

 

Then after that Term1 + Term2= combined result in the following way

for R-01 71+86 = 157           157/200 = 78.5%

for R-02 91+ 80 = 171           171/200 = 85.5%

based on this grade should be filled

SessionName

Subject

MinMark

MaxMark

Lowest %

Highest %

Grade

S-19

Englsih

157

171

78.5

85.5

 

how to achieve it?

Posted 3 months ago Modified on 3 months ago

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