user908759 user908759 - 6 months ago 27
SQL Question

Access SQL Query Results in Ranges

If you had a DB that had a Class table and a Student table and Class has many Students. And you wanted to create a query to find all the students grades that fit within a specific range how would you do it? To keep it simple lets say you have 10 students in a class and their grades were:

100, 97, 96, 95, 90, 85, 80, 75, 74, and 70

And you want the grade broken up in the following ranges:

Grades 100-90, Grades 89-80, and Grades 79-70

I would expect the resulting columns from the query to be:

[Grades 100-90] [Grades 89-80] [Grades 79-70]
[ 5 ] [ 2 ] [ 3 ]


How could this be done? and What would the query look like?

Answer Source

You can do this with conditional aggregation:

select sum(iif(grade between 90 and 100, 1, 0)) as grades_100_90,
       sum(iif(grade between 80 and 89, 1, 0)) as grades_80_89,
       . . .
from t;