How to count some data percent based on table? Thanks a lot!!

[xpengi]

How to create a query to count data as percent based on table? Thanks a lot!!

Hi,

I have been stifled with question for 5 days...my boss will lose his patience....help please!!

i have a table "students" in my case as below,
Student# -------------------City --------------------------Amount
1 -----------------------toronto -------------------------1000
2 -----------------------ottawa --------------------------3000
3 -----------------------toronto --------------------------4000
4 -----------------------ottawa --------------------------8000
5 -----------------------montreal ------------------------11000
6 -----------------------vancouver---------------------- 15000
7 -----------------------toronto
8 -----------------------ottawa
9 -----------------------montreal
10 -----------------------vancouver



I want to build a query to generate the output is like below,

RANGE(amount)-----------------NUMBER OF STUDENTS ------------------PERCENT of TOTAL(%)
0-5000--------------------------------- 3 ------------------------------------------30%
5001-10000----------------------------1------------------------------------------10%
10001-15000--------------------------2------------------------------------------20%
null value ------------------------------4------------------------------------------40%

how to build such a query? i need paint a bar & pie chart based on this query.

i got 2 ideas from some experts in this forum,

1.use partition function, but still problematic to work.

SELECT Partition(amount, 0, 20000, 5000),
Count(*) AS [NUMBER OFSTUDENTS]
FROM students
GROUP BY Partition(amount, 0, 20000, 5000)


2. create a new table with "RANGE(amount)"...., then apply subquery to obtain wanted data. it works, BUT cannot showing "null value" and i don't know how to get data of "PERCENT of Total(%)".

Thanks a thousand!!!

Peter