Home » SQL & PL/SQL » SQL & PL/SQL » query on emp table (Oracle, 10g, windows XP)
query on emp table [message #334607] Thu, 17 July 2008 06:18 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi
I need the HINT for the following query

Display the empno,ename, sal for all employees who earn more than the average sal and who work in a department with any employee

I tried the following:(In select clause the columns are different)
SELECT   SAL,
         COUNT(* )
FROM     EMP
WHERE    SAL >  ANY (SELECT   AVG(SAL)
                     FROM     EMP
                     GROUP BY DEPTNO)
GROUP BY DEPTNO,
         SAL;

I even tried with the > all but none gave correct result..
Please excuse me if this is a newbie question.

Thanks
Jyothsna



Re: query on emp table [message #334611 is a reply to message #334607] Thu, 17 July 2008 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let's analyze the question.

"Display the empno,ename, sal", so the select list must contain these columns.

"who earn more than the average sal ", there it is not clear (given the last part) if the average is the global average or the average of salary in the employee department. Assuming it is the global average, how do you get it?

" who work in a department with any employee", if you select from emp then you can't have data from a department that has no employee but maybe I misunderstood the sentence.

Regards
Michel
Re: query on emp table [message #334616 is a reply to message #334607] Thu, 17 July 2008 06:43 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi
There would be a chance to misunderstand the query as this is not much clear for the first time.

Ex:
The avg(sal) for deptno 10 = 2916. If The employees who belong to only dept 10 earns more than 2916, then his details must be displayed..
Like the same approach for dept 20 and 30. also

I think now i am somewhat clear to this query..

Re: query on emp table [message #334617 is a reply to message #334616] Thu, 17 July 2008 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, now first build the query to get the average salary for each department, then query from emp the employee which salary is greater than this value.
You query almost does all what you want, you just have to select the correct field in the end.

Regards
Michel
Re: query on emp table [message #334620 is a reply to message #334617] Thu, 17 July 2008 07:17 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
My doubt is:
SQL> SELECT   DEPTNO,
         ROUND(AVG(SAL))
FROM     EMP
GROUP BY DEPTNO;

    DEPTNO ROUND(AVG(SAL))
---------- ---------------
        30            1567
        20            2175
        10            2917

Here deptno10's avg(sal) is 2917
But check the o/p of the query i wrote:
SELECT   SAL,deptno,
         COUNT(* )
FROM     EMP
WHERE    SAL >  ANY (SELECT   AVG(SAL)
                     FROM     EMP
                     GROUP BY DEPTNO)
GROUP BY DEPTNO,
         SAL;

       SAL     DEPTNO   COUNT(*)
---------- ---------- ----------
      5000         10          1
      3000         20          2
      2450         10          1
      1600         30          1
      2850         30          1
      2975         20          1


In the above o/p: the salary 2450 for dept10 which is
less than the avg(sal) of deptno10 i.e.,2917 , and is the wrong o/p:

So HINT me to change my query:
Re: query on emp table [message #334621 is a reply to message #334607] Thu, 17 July 2008 07:22 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
This is happend because of Any clause in the sub query.First data will be filtered then the grouping will be taken place.

[Updated on: Thu, 17 July 2008 07:23]

Report message to a moderator

Re: query on emp table [message #334622 is a reply to message #334620] Thu, 17 July 2008 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to make a relation between the deptno from outer emp and the deptno from subquery.

Regards
Michel
Re: query on emp table [message #334624 is a reply to message #334621] Thu, 17 July 2008 07:30 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Now i'm getting the required output.
SQL> select sal,deptno,count(*)
  2  from emp
  3  where sal>any(select avg(sal)
  4  from emp)
  5  group by deptno,sal;

       SAL     DEPTNO   COUNT(*)
---------- ---------- ----------
      5000         10          1
      2975         20          1  
      2450         10          1
      3000         20          2
      2850         30          1


Thanks for your support Smile
Re: query on emp table [message #334626 is a reply to message #334624] Thu, 17 July 2008 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that the query gives the employee with salary greater than the overall average and not the average of employee department.
It may or may not be correct depending on the actual requirement.

Regards
Michel
Re: query on emp table [message #334646 is a reply to message #334607] Thu, 17 July 2008 09:24 Go to previous messageGo to next message
sivanagahari.devarapalli@
Messages: 1
Registered: May 2008
Junior Member
Hi,
First let me express my understanding about question:

===============================================================
Display emp details whose salary is greater than average salary of department in which he is working.
===============================================================

----------------------------------------------------------------
According to this first try to get each department's AVG salary:
----------------------------------------------------------------

SELECT deptno,ROUND(avg(sal)) avg_sal
FROM emp
GROUP BY deptno;


This query results:::

DEPTNO AVG_SAL
--------- ----------
30 1567
20 2175
10 2917

----------------------------------------------------------------
Now try to form final statement using the above statement as part of FROM clause "INLINE VIEW" :
----------------------------------------------------------------
SELECT empno,ename,sal,e.deptno
FROM emp e, (SELECT deptno,ROUND(avg(sal)) avg_sal
FROM emp
GROUP BY deptno) i
WHERE e.deptno=i.deptno AND e.sal >avg_sal;




EMPNO ENAME SAL DEPTNO
-------- ---------- ---------- ----------
7698 BLAKE 2850 30
7499 ALLEN 1600 30
7902 FORD 3000 20
7788 SCOTT 3000 20
7566 JONES 2975 20
7839 KING 5000 10


Excuse me for the format & confusion if any.

thanks & Regards,

Siva Naga Hari
Re: query on emp table [message #334671 is a reply to message #334646] Thu, 17 July 2008 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Excuse me for the format

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: query on emp table [message #334780 is a reply to message #334646] Fri, 18 July 2008 00:19 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi,
Michel is correct, as my query is taking the avg of all departments not a particular department. Now my confusion is clear
and query written by SIVA is correct according to my requirement.

Thank you again.
Re: query on emp table [message #334781 is a reply to message #334780] Fri, 18 July 2008 00:32 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I encourage you to rewrite Siva's query the same way you wrote yours (using a subquery instead of an inline view), just as an exercise.

Regards
Michel
Previous Topic: Deferred Constraint Without Dropping Existing Constraint
Next Topic: website for PL/SQL
Goto Forum:
  


Current Time: Sun Dec 11 00:32:47 CST 2016

Total time taken to generate the page: 0.10661 seconds