Help for building a statistical query, thanks a lot!

[xpengi]

Hi,

I want build a SQL query to generate statistical result from
table(students) in MS Access as below:

Student# City Amount
001 toronto 1000
002 ottawa 3000
003 toronto 4000
004 ottawa 8000
005 montreal 11000
006 vancouver 15000

i build a query:

SELECT Amount AS ["0~5000"], COUNT(1) AS ["NUMBER OF STUDENTS"]
FROM students
WHERE amount > -5000

UNION SELECT amount AS ["5000~10000"], COUNT(1) AS ["NUMBER O
STUDENTS"]
FROM students
WHERE amount < -5000 AND amount > -10000;

i want to get result of how many students who have loan amount i
between 0-5000, 5000-10000, 10000-15000?

output:

Amount***************** Number of students
0-5000***************** 3
5000-10000***************** 1
10000-15000***************** 2


Thanks a lot.

Pete

 

[Duane Hookom]

You might want to use the Partition() function. This will work as long as
your ranges are consistent.

 

[xpengi]

Thanks a thousand!