Report Card Query in MySQL

A real report card

A report card query is a classic school project: from a table of marks, compute each student's total, percentage, grade and pass/fail. It brings together SUM, GROUP BY and CASE — a great practical exercise.

The marks table

-- one row per student per subject
+------------+---------+-------+
| student_id | subject | marks |
+------------+---------+-------+
|          1 | Math    |    88 |
|          1 | Science |    76 |
|          1 | English |    92 |
+------------+---------+-------+

This normalized layout (a row per subject) makes totals and averages easy, and lets you add subjects without changing the table.

Total and percentage

SELECT student_id,
    SUM(marks) AS total,
    ROUND(SUM(marks) / (COUNT(*) * 100) * 100, 2) AS percent
FROM marks
GROUP BY student_id;

GROUP BY student, SUM the marks for the total, and divide by the maximum (subjects × 100) for the percentage, rounded to two places.

Grade with CASE

SELECT student_id,
    SUM(marks) AS total,
    CASE
        WHEN SUM(marks)/(COUNT(*)*100)*100 >= 90 THEN 'A'
        WHEN SUM(marks)/(COUNT(*)*100)*100 >= 75 THEN 'B'
        WHEN SUM(marks)/(COUNT(*)*100)*100 >= 33 THEN 'C'
        ELSE 'F'
    END AS grade
FROM marks
GROUP BY student_id;

A CASE on the percentage turns the number into a letter grade, right in the query.

Pass or fail

SELECT student_id,
    CASE WHEN MIN(marks) >= 33 THEN 'Pass' ELSE 'Fail' END AS result
FROM marks
GROUP BY student_id;

A student passes only if every subject is at least the pass mark. Checking MIN(marks) >= 33 catches a failure in any single subject.

Ranking students

SELECT student_id, SUM(marks) AS total,
    RANK() OVER (ORDER BY SUM(marks) DESC) AS rank_no
FROM marks
GROUP BY student_id;

In MySQL 8.0+, RANK() assigns positions by total marks, giving each student their class rank. On older MySQL, just ORDER BY total DESC.

Common mistakes

• Storing all subjects in one row. Use one row per subject for easy SUM/AVG.
• Wrong percentage divisor. Divide by subjects × max marks, not a fixed number.
• Grading before pass check. A low subject can fail a high average.
• Forgetting GROUP BY. Aggregates need it to work per student.

Summary

• Store marks one row per student per subject.
• Total = SUM(marks); percentage divides by subjects × 100.
• Grade with a CASE on the percentage.
• Pass/fail uses MIN(marks) against the pass mark.
• Rank with ORDER BY total or RANK() (MySQL 8.0+).

Next, learn grouping in GROUP BY and HAVING, or build the full marksheet database project.