• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql : unable to understand why query does not work

 
thejwal pavithran
Ranch Hand
Posts: 119
  • 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?
 
Greg Charles
Sheriff
Posts: 2984
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Because you call the field job_title, and the query uses job?
 
thejwal pavithran
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
question edited...please see now
 
Greg Charles
Sheriff
Posts: 2984
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • 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:
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • 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.
 
chris webster
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • 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:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic