aspose file tools*
The moose likes JDBC and the fly likes Query question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query question" Watch "Query question" New topic
Author

Query question

Elahe Shafie
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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


Thanks,
Elahe
Leslie Chaim
Ranch Hand

Joined: May 22, 2002
Posts: 336
You are missing a 'group by'
do this
SELECT (tPoints.coef2 - Min(coef2)) / (( Max(coef2) - Min(coef2))/5)
FROM tPoints
group by tPoints.coef2;


Normal is in the eye of the beholder
Elahe Shafie
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: May 22, 2002
Posts: 336
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?
Elahe Shafie
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: May 22, 2002
Posts: 336
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
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: May 22, 2002
Posts: 336
Which database do you have?
Not all databases support subqueries as a column.
How about the solution which I gave?
Elahe Shafie
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: May 22, 2002
Posts: 336
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
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
Jamie it works!!!
Both you are just great
Many many thanks to you guys...
Elahe
Elahe Shafie
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
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
Ranch Hand

Joined: May 22, 2002
Posts: 336
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...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query question
 
Similar Threads
Pausing and restarting other threads at arbitrary times
oracle min function
Query help
SCJA part1 exam duration?
Round the float number?