hi guys, how to use Partition() function or any idea in my case? urgent help

[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 but does not work:

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


how to do that? Is possible to build one query to carry out it?
(my boss are waiting for this, really appreciate for quick response)

Thanks a lot.

Pete

 

[Marshall Barton]

Hi,

I want build a SQL query to generate statistical result from a
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 but does not work:

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 OF
STUDENTS"]
FROM students
WHERE amount < -5000 AND amount > -10000;

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

output:

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

Not exactly, but close:

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

 

[xpengi]

hi,

it seems not work, anything still wrong?

with the query,

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


the output is:

RANGE NUMBER OF STUDENTS
62 <----- there 62 student
who have no loan(null value in table)
: -1 20001


Thanks

 

[Marshall Barton]

it seems not work, anything still wrong?

with the query,

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


the output is:

RANGE NUMBER OF STUDENTS
62 <----- there 62 students
who have no loan(null value in table)
: -1 20001

What is your question?

Is it that the students that do not have a loan amount are
not assigned to a range? If so, then use the Nz function:
Partition(Nz(amount,0), 0, 20000, 5000)

Or, is the question that you do not want to see those
students at all? If this is the question, then add a Where
clause to filter those students out of the query's records:

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

 

[xpengi]

hi,

i mean, after excuting this query,

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

the output is:

RANGE -----------------------------NUMBER OF STUDENTS

: -1 -----------------------------------6

the partition function seems not work here. what is ": -1" mean?



I want the output is like below,

RANGE----------------------------NUMBER OF STUDENT
------------------PERCENT(%)
0-5000---------------------------------
------------------------------------------50%
5001-10000----------------------------1------------------------------------------16.6667%
10001-15000--------------------------2------------------------------------------33.3333%

how to build such a query?

Thanks a thousand!!

 

[Marshall Barton]

i mean, after excuting this query,

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

the output is:

RANGE -----------------------------NUMBER OF STUDENTS

: -1 -----------------------------------6

the partition function seems not work here. what is ": -1" mean?



I want the output is like below,

RANGE----------------------------NUMBER OF STUDENTS
------------------PERCENT(%)
0-5000--------------------------------- 3
------------------------------------------50%
5001-10000----------------------------1------------------------------------------16.6667%
10001-15000--------------------------2------------------------------------------33.3333%

We've already built the query according to the sample data
you provided (we'll worry about the percents adter we get
the rest of it to produce the correct results.

The :-1 is the items that fall below the first partition
value, which is zero. If you have any amounts that are over
20000, you'll also get a 20000: partition.

All that means is all 6 of your amount values are less than
zero. Double check that this is the case and if so, change
amount to -amount in both the partition calls.