Forums Register Login

Query question

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
You are missing a 'group by'
do this
SELECT (tPoints.coef2 - Min(coef2)) / (( Max(coef2) - Min(coef2))/5)
FROM tPoints
group by tPoints.coef2;
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
elahe:
You do need group by, here is why.
Imagine you say

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

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:

Got it?
That's why you need group by, this is all basic SQL.
Now, to your problem -- what is the error you get?
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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;
+Pie Number of slices to send: Send
 

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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
Which database do you have?
Not all databases support subqueries as a column.
How about the solution which I gave?
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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..
+Pie Number of slices to send: Send
Jamie it works!!!
Both you are just great
Many many thanks to you guys...
Elahe
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
 

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,

That's if Access has the count() function...
We're being followed by intergalactic spies! Quick! Take this tiny ad!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1281 times.
Similar Threads
SCJA part1 exam duration?
Pausing and restarting other threads at arbitrary times
oracle min function
Query help
Round the float number?
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 07:19:49.