Win a copy of Spark in Action this week in the Open Source Projects forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

sql : unable to understand why query does not work

 
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Sheriff
Posts: 3036
12
Mac IntelliJ IDE Python VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Because you call the field job_title, and the query uses job?
 
sinatra roger
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
question edited...please see now
 
Greg Charles
Sheriff
Posts: 3036
12
Mac IntelliJ IDE Python VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The nested query should not be needed:The WHERE clause is processed before the GROUP BY.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic