CODEWITHSUNDEEP
sqlms-access
user908759
user908759

Reputation: 1355

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?

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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;

Upvotes: 1

Related Questions