CODEWITHSUNDEEP
sqldatabasesql-server-2008
Kwame
Kwame

Reputation: 45

How to calculate student grade with SQL

I want to calculate grades automatically from a SQL Server database with my query but I'm having a lot of errors. Any help?

This is what I've tried but it's error prone.

Student   Asst1   Asst2   Exam
-------   ------   ------  ------
John          12         22        38

Grade query in SQL:

SELECT 
   *, 
   (Asst1 + Asst2 + Exam) AS TotalMark, 
   (IF(TotalMark > 84.5, 'A', IF (TotalMark > 64.5, 'B', 
       IF(TotalMark > 49.5, 'C', IF (TotalMark > 29.5, 'D', 'E'))))) AS Grade 
FROM Results

Something like that. (Although it is from memory and it didn't double-check syntax)

Upvotes: 0

Views: 24423

Answers (1)

Aditya Kakirde
Aditya Kakirde

Reputation: 5225

Try this - 

 SELECT R.STUDENT, (Asst1+Asst2+Exam) AS TotalMark, 
    CASE WHEN (Asst1+Asst2+Exam) > 84.5
    THEN 'A'
    WHEN (Asst1+Asst2+Exam) < 84.5 AND (Asst1+Asst2+Exam) > 64.5
    THEN 'B'
    WHEN (Asst1+Asst2+Exam) < 64.5 AND (Asst1+Asst2+Exam) > 49.5
    THEN 'C'
    WHEN (Asst1+Asst2+Exam) < 49.5 AND (Asst1+Asst2+Exam) > 29.5
    THEN 'D'
    ELSE 'E'
    END AS GRADE
    FROM RESULTS R;

Upvotes: 3

Related Questions