Table:
id | score |
1 | 56 |
2 | 91 |
3 | 67 |
4 | 54 |
5 | 56 |
6 | 69 |
7 | 61 |
8 | 83 |
9 | 99 |
MS Access
SELECT Switch(score < 60, "less 60",
score < 80, "60 - 80",
score < 100 , "80-100"
) as score_type,count(id) as stu_cnt
FROM subject_table
group by Switch(score < 60, "less 60",
score < 80, "60 - 80",
score < 100 , "80-100"
)
MySQL
SELECT (case when score < 60 then "less 60" when score < 80 then "60 - 80" when score < 100 then "80-100" else "other" end) as score_type,count(id) as stu_cnt FROM `subject_table` group by (case when score < 60 then "less 60" when score < 80 then "60 - 80" when score < 100 then "80-100" else "other" end)