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
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
Last edited: