How can I use the COUNT and GROUP BY along with the CASE commands in SQLlite to show the number of students with each letter grade?

Question

I am trying to use the SQL commands COUNT and GROUP BY to show the number of students with each letter grade, but I'm having difficulty in doing so. A new column that I created contains a letter grade based on a specific range of numeric grades. For example, if the grade is 90 or higher, then it's an "A". If the grade is between 80 and 89, then it's a "B". If the grade is between 70 and 79, then it's a "C". If the grade is between 60 and 69, then it's a "D." Lastly, if the grade is less than 60, then it's an "F". However, I managed to successfully create both the letter grade column and the column containing the number of students grouped by letter grade.

I tried the following SQL query to get the expected result, but it didn't work:

SELECT 
    name,
    number_grade,
    ROUND((fraction_completed * 100)) AS percent_completed,
    CASE 
    WHEN number_grade >= 90 THEN 'A'
    WHEN number_grade >= 80 THEN 'B'
    WHEN number_grade >= 70 THEN 'C'
    WHEN number_grade >= 60 THEN 'D'
    ELSE 'F'
    END AS letter_grade, 
    COUNT(number_grade) AS num_of_students
FROM student_grades
GROUP BY letter_grade;

However, even though the actual results are a column containing the letter grades and a column containing the number of students grouped by letter grade, with no error results, I still didn't get the correct answer.

SUM(CASE WHEN number_grade >= 90 THEN 1 ELSE 0 END) AS A_count might work — LMC
– LMC, Commented May 10 at 14:27
No need to use SUM which requires an ugly ELSE clause, simply write COUNT(CASE WHEN number_grade >= 90 THEN 1 END) — Jonas Metzler
– Jonas Metzler, Commented May 10 at 15:23
You are selecting the name, implying one row per student. You're also trying to get the number of students with each grade, implying one row per grade. This contradiction is why it's important to write a minimal reproducible example including the results you want. — MatBailie
– MatBailie, Commented May 10 at 15:45
Please read : Why should I provide a Minimal Reproducible Example, even for a very simple SQL query? — MatBailie
– MatBailie, Commented May 10 at 15:45

MatBailie · Accepted Answer · 2025-05-10 15:40:02Z

0

You can use conditional COUNT for that purpose

CREATE TABLE student_grades(number_grade  int,fraction_completed  DOUBLE)

INSERT INTO student_grades VALUES (91,96.5),(81,20), (71,10)

SELECT * FROM
student_grades

number_grade	fraction_completed
91	96.5
81	20
71	10

SELECT

    number_grade,
    ROUND((fraction_completed * 100)) AS percent_completed,
    CASE 
    WHEN number_grade >= 90 THEN 'A'
    WHEN number_grade >= 80 THEN 'B'
    WHEN number_grade >= 70 THEN 'C'
    WHEN number_grade >= 60 THEN 'D'
    ELSE 'F'
    END AS letter_grade, 
    COUNT(number_grade) AS num_of_students

,SUM(CASE  WHEN number_grade >= 90 THEN 1 ELSE 0 END) AS count_A
,SUM(CASE  WHEN number_grade >= 80 AND number_grade < 90 THEN 1 ELSE 0 END) AS count_B
,SUM(CASE  WHEN number_grade >= 70 AND number_grade < 80 THEN 1 ELSE 0 END) AS count_C
,SUM(CASE  WHEN number_grade >= 60 AND number_grade < 70 THEN 1 ELSE 0 END) AS count_D
FROM student_grades
GROUP BY letter_grade;

number_grade	percent_completed	letter_grade	num_of_students	count_A	count_B	count_C
91	9650	A	1	1	0	0
81	2000	B	1	0	1	0
71	1000	C	1	0	0	1

fiddle

edited May 10 at 15:40

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

answered May 10 at 15:16

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonas Metzler May 10 at 15:26

No need to use SUM which requires an ugly ELSE clause, we can simply write COUNT(CASE WHEN number_grade >= 90 THEN 1 END). Modified version of your fiddle here

MatBailie May 10 at 15:41

Why is number_grade included in the SELECT block?

nbk May 11 at 13:42

because it is in the original query

Collectives™ on Stack Overflow

How can I use the COUNT and GROUP BY along with the CASE commands in SQLlite to show the number of students with each letter grade?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related