26/07/2020  |   

Table：

idscore
156
291
367
454
556
669
761
883
999
Subject_table

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)