Originally posted by elahe shafie:
Jamie thank you you are great...I will follow your way and let you know, but let ask you one thing, why this query return 0 ?it shouldn't be 0 based on data that I sent to you...
SELECT tPoints.coef2-Max(tPoints.coef2) FROM tPoints group by tPoints.coef2;
What is wrong here?
Many thanks,
Eahe
This is easier to illustrate using an example:
say you have a table called sales:
-------------------------------------
customer sale amount salesman
-------------------------------------
John 500 Bob
Sally 500 Bob
Peter 70 Bob
Jim 2000 Sam
Sue 2000 Sam
-------------------------------------
The max function on sales used with a group by on salesman will give you the maximum sale amount for each salesman. 'select salesman, max(sale amount) from sales group by salesman' would produce:
Bob 500
Sam 2000
The max function on sales amount used with a group by on sales amount ( like your situation ) would produce:
500
70
2000
Not what you want. You know that the largest sales amount of all the 500's is 500 and the largest sales amount of 70 is 70, etc. This is what happened in your situation:
SELECT tPoints.coef2-Max(tPoints.coef2) FROM tPoints group by tPoints.coef2;
if you had these values in your table:
-----
coef2
-----
10
10
5
This would produce:
0
0
Why? You group by the coef2 value(group all the 10's together and group all the 5's together). What is the max of all the coef's of value 10? it is 10. so your result is 10 - 10 which is 0. The next value is 5. What is the max coef2 of all the coef2's with a value of 5? it's 5. So you get 5 - 5.
What I think you want:
group by coef2, so you don't get any repeated values, but subtract the maximum coef2 in the table:
'select coef2 - ( select max ( coef2 ) from tPoints ) from tPoints group by coef2'
10 - 10
5 - 10
anyways, you might want to read up on the group by clause if you're having trouble understanding it.
Jamie