File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes sql : unable to understand why query does not work Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql : unable to understand why query does not work" Watch "sql : unable to understand why query does not work" New topic
Author

sql : unable to understand why query does not work

thejwal pavithran
Ranch Hand

Joined: Feb 11, 2012
Posts: 117
hi i have an emp table with the fields empid,ename,sal,job_title,DEPTNO....

i need to find the average salaries of all job titles belonging to department 3.

i have written a query : select avg(sal) from emp group by job_title having deptno = 3;

but the query doesnt work...please tell what im missing?


on job hunt
Greg Charles
Sheriff

Joined: Oct 01, 2001
Posts: 2835
    
  11

Because you call the field job_title, and the query uses job?
thejwal pavithran
Ranch Hand

Joined: Feb 11, 2012
Posts: 117
question edited...please see now
Greg Charles
Sheriff

Joined: Oct 01, 2001
Posts: 2835
    
  11

Oh, in that case, you need deptno in the select fields to be able to use it in the HAVING clause:


I'm betting that's not what you want though. The GROUP BY will compress rows for multiple deptnos together, so you'll sort of arbitrarily filter out data. You want to filter out all the rows that aren't deptno 3 before you group together data. Something like:
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

The nested query should not be needed:The WHERE clause is processed before the GROUP BY.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1614
    
  13

You also need to include your GROUP BY columns in your SELECT e.g. This should give you results like:
Obviously your data will be different.

HAVING is like a WHERE clause for grouped data e.g. you could use it to get results only where average salary is > 1500:


No more Blub for me, thank you, Vicar.
 
wood burning stoves
 
subject: sql : unable to understand why query does not work
 
Similar Threads
First Record in the Rank()
SQL query help
Simple Querry
Order by query..
Date format