Query question

[Elahe Shafie]

Hi Friends,
Could you please tell me what is wrong in my query which it doesn't execute?
SELECT (tPoints.coef2 - Min(coef2)) / (( Max(coef2) - Min(coef2))/5)
FROM tPoints;
Many thanks,
Elham

[Leslie Chaim]

You are missing a 'group by'
do this
SELECT (tPoints.coef2 - Min(coef2)) / (( Max(coef2) - Min(coef2))/5)
FROM tPoints
group by tPoints.coef2;

[Elahe Shafie]

Why do I need group by?(In my case I don't need group by) and even with group by it didn't work
Any idea?
Elahe

[Leslie Chaim]

elahe:
You do need group by, here is why.
Imagine you say

select max(salary)  from emp

This query returns the max from the entire table.
Now what do you think this would do:

select first_name, last_name max(salary)  from emp

Now, this is contradicting. From one side you ask for the max(..) while then you ask for a specific column, such as first_name. Which first_name, last_name should be reported?
So you need:

select first_name, last_name max(salary)
  from emp
 group by first_name, last_name

Got it?
That's why you need group by, this is all basic SQL.
Now, to your problem -- what is the error you get?

[Elahe Shafie]

Got it completely Leslie now I understand I do need group by...
Okay my problem is I am trying to make a guery form this formula:
n= Round((X-Xmin)/(Xmax-Xmin/5)))-0.5)
SELECT Round(((tPoints.coef2-Max(tPoints.coef2))/((Max(tPoints.coef2)-Min(tPoints.coef2))/5))-0.5) FROM tPoints group by tPoints.coef2;
But it doesn't return ant thing?
I don't know what is wrong here?...
Elahe

[Elahe Shafie]

and my table column is:
coef2
---------
1.5173E-06
1.5173E-06
1.5173E-06
1.5173E-06
1.5173E-06
1.5173E-06
1.5173E-06
1.5234311E-06
1.5250309E-06
9.7765472E-07
9.7765472E-07
9.7765472E-07
1.1712652E-06

[Jamie Robertson]

Elahe, if you want to make a calculation on grouped columns, then you should use subqueries.
eg.
SELECT (tPoints.coef2 - (select Min(coef2) from tPoints )) / (( select Max(coef2) from tpoints) - (select Min(coef2) from tPoints )/5))
FROM tPoints group by tPoints;
The above query should give you
(column_value - minimum coef2 in the tPoints table) / (maximum coef2 in the tPoints table - minimum coef2 in the tPoints table) \5)
for every unique coeff in the table. Don't know if that is what you want, but I don't see why you are taking the max and min from the same column you are grouping by ( it will yield the same values as the grouped by column )
example, if coef2 was 1.5173E-06
then that would also be the max and min value in the grouped by clause. Anyways, give the subqueries a try.
Jamie

[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

[Elahe Shafie]

I guess it should be your answer if I learned correctly. right?
SELECT (tPoints.coef2 - (select Min(coef2) from tPoints ))
FROM tPoints group by tPoints.coef2;

[Jamie Robertson]

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

[Leslie Chaim]

Try this without subqueries:
SELECT
Round(((t1.coef2-Max(t2.coef2))/((Max(t2.coef2)-Min(t2.coef2))/5))-0.5)
FROM tPoints t1, tPoints t2
GROUP BY t1.coef2

[Elahe Shafie]

Thanks again Jamie and now I got it completely.
Now here is my final guery based on this formula:
n=Round (((X - Xmin)/((Xmax - Xmin)/5))- 0.5)
SELECT Round(((coef2 - (select Min(coef2) from tPoints )) / ((( select Max(coef2) from tpoints) - (select Min(coef2) from tPoints ))/5)) - o.5)
FROM tPoints group by tPoints.coef2;
But when I want to run it,it says "invalid use of !,.,()in guery expression
I checked several times but couldn't find any extra parantises in my query could you please help me what I am doing wrong here?
Many thanks,
Elahe

[Leslie Chaim]

Which database do you have?
Not all databases support subqueries as a column.
How about the solution which I gave?

[Elahe Shafie]

I am using MS-Access and I guess it support sub query... am I right? so I changed the query like this and got the result but not sure the data is correct or not:
Is this correct?

SELECT Round ((((coef2 - (select Min(coef2) from tPoints )) / ((( select Max(coef2) from tpoints) - (select Min(coef2) from tPoints ))/5)))-0.5)
FROM tPoints
GROUP BY tPoints.coef2;
Elahe
PS.Your query didn't work properly.

[Jamie Robertson]

Elahe:
This is cut and pasted out of the SQL View tool in MSAccess that worked for me:
SELECT tPoints.coef2, Round(((coef2-(select min(coef2) from tPoints ))/(((select max(coef2) from tPoints )-(select min(coef2) from tPoints ))/5))-0.5) AS Expr1
FROM tPoints
GROUP BY tPoints.coef2;
Also working is Leslie's suggestion:
SELECT t.coef2, Round (((t.coef2 - min(p.coef2))/((max(p.coef2) - min(p.coef2))/5))- 0.5)
FROM tPoints AS t, tPoints AS p
GROUP BY t.coef2;
Jamie

[Leslie Chaim]

I do not have experience with MS access, but even if it *did* support subqueries does not mean that it supports it in columns.
In other words, it may support:
select ...
from ...
where ... = (subquery)
However, the very same database may not support:
select ..., (subquery), ...
from ...
which is what you are trying to do.
Try this ...
SELECT
Round(((t1.coef2-Max(t2.coef2))/((Max(t2.coef2)-Min(t2.coef2))/5))-0.5)
FROM tPoints AS t1, tPoints AS t2
GROUP BY t1.coef2
I added the 'AS' keyword which is required by some databases, I am not sure if Access is one of them..

[Elahe Shafie]

Jamie it works!!!
Both you are just great
Many many thanks to you guys...
Elahe

[Elahe Shafie]

I have one more question as well as we know the result for this query:
SELECT tPoints.coef2, Round(((coef2-(select min(coef2) from tPoints ))/(((select max(coef2) from tPoints )-(select min(coef2) from tPoints ))/5))-0.5) AS Expr1
FROM tPoints
GROUP BY tPoints.coef2;
Coef2 Expr1
----------- --------
9.776547162E-07 0
1.171265184E-07 1
1.5173E-06 4
1.5173E-06 4
1.5173E-06 4
1.5173E-06 4
1.523431121E-06 4
1.525030947E-06 4
the question is can I have count(Expr1) from second column with the same query that we have?
Thanks,
Elahe

[Elahe Shafie]

Coef2 Expr1
----------- --------
9.776547162E-07 0
1.171265184E-07 1
1.5173E-06 4
1.5173E-06 4
1.5173E-06 4
1.5173E-06 4
1.523431121E-06 4
1.525030947E-06 4

[Leslie Chaim]

Originally posted by elahe shafie:

...
the question is can I have count(Expr1) from second column with the same query that we have?
Thanks,
Elahe

I am not sure ... but try this,

SELECT t.coef2, 
       Round (((t.coef2 - min(p.coef2))/((max(p.coef2) - min(p.coef2))/5)) - 0.5) AS EXPR,
       count(1) AS COUNT
  FROM tPoints AS t, tPoints AS p
 GROUP BY t.coef2;

That's if Access has the count() function...