Home » SQL & PL/SQL » SQL & PL/SQL » stuck with output
stuck with output [message #298016] Mon, 04 February 2008 10:46 Go to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
Hi there, I am stuck with this:

I did this:
select avg(e.salary) sal, e.department_id, e.first_name name
from department d, employee e where d.department_id = e.department_id group by
e.department_id, e.first_name;


SAL DEPARTMENT_ID NAME
---------- ------------- ---------------
1600 30 KEVIN
2850 13 JEAN
2750 23 LYNN
1250 30 CYNTHIA
800 20 JOHN
2850 30 MARION
950 30 FRED
3000 20 JENNIFER
2875 12 GRACE
1250 23 GREGORY


and
select avg(e.salary) sal2, e.department_id from department d, employee e
where d.department_id = e.department_id group by e.department_id ;

SAL2 DEPARTMENT_ID
---------- -------------
1566.66667 30
1850 34
1300 43
1470 13
2175 20
2200 14
1800 24
1500 23
2418.75 12
2916.66667 10

How can I actually show the NAME of employee whose salary > average salary of the department?
For example, Kevin's salary is 1600 and he's on dept 30.. The average salary for dept 30 is 1566.6

therefore his details will be shown.
If you see the last one, Gregory who is in dept 23 and his salary is 1250 and the average salary
for dept 23 is 1500. Therefore, his detail will not be shown.

I think I need to use co-related query for this but I am stuck on trying to get the output I want.
Re: stuck with output [message #298017 is a reply to message #298016] Mon, 04 February 2008 10:54 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
You have already been pointed towards the forum guide Please have the courtesy to follow it. This is clearly schoolwork of some sort. You are right, a correlated sub query will do what you need. Show us what you have tried in this regard

PS, why on earth have you posted this in the pl/sql experts section. It is neither pl/sql nor does it require expert assistance.

[Updated on: Mon, 04 February 2008 10:55]

Report message to a moderator

Re: stuck with output [message #298019 is a reply to message #298016] Mon, 04 February 2008 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once more read and follow 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.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

In addition, you're a newbie post in newbie or homework forum and not in expert one.

Regards
Michel

[Edit: I see pablolee and I agree on your behaviour.
Now, if you want help, follow the rules]

[Updated on: Mon, 04 February 2008 10:58]

Report message to a moderator

Re: stuck with output [message #298024 is a reply to message #298016] Mon, 04 February 2008 11:32 Go to previous messageGo to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
I'm sorry about that.
I shall follow the rules next time.

Ok I did this :

select sal, deptid, name from (
select avg(e.salary) sal, e.department_id deptid, e.first_name name
from department d, employee e where d.department_id = e.department_id group by
e.department_id, e.first_name )
group by sal, deptid, name having avg(sal) < sal ;

but the result turns out to be 'no rows selected'.
Re: stuck with output [message #298025 is a reply to message #298024] Mon, 04 February 2008 11:33 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
No, don't follow them next time. Follow them now. Edit your post and format it as per the instructions in the guidelines. This is easy to do, please pay us the courstesy of adhering to the guidelines.
Re: stuck with output [message #298195 is a reply to message #298025] Tue, 05 February 2008 05:19 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
select x.emp_name,x.sal 
from emp x , 
     (select avg(e.sal) as avgsal,e.dept 
      from emp e,dept d 
      where e.dept=d.deptno group by e.dept) b 
where x.dept=b.dept and x.sal > b.avgsal

[Updated on: Tue, 05 February 2008 05:31] by Moderator

Report message to a moderator

Re: stuck with output [message #298198 is a reply to message #298195] Tue, 05 February 2008 05:30 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Close but no cigar. Do you think that what you posted there is any easier to read than what you posted before? The whole point of formatting is to make it easier to read.
SELECT x.emp_name
            ,x.sal 
      FROM emp x 
            ,(SELECT AVG(e.sal) AS avgsal
              ,e.dept 
              FROM emp e
                  ,dept d 
              WHERE e.dept=d.deptno 
              GROUP BY e.dept) b 
      WHERE x.dept=b.dept 
      AND x.sal > b.avgsal

Now, why on earth are you even referencing the departments table in the from clause. You are not using any columns from that table.
Here is a bit of pseudo code to help you on your way. And in future PLEASE try to format your code properly.
      SELECT required_columns
      FROM your_table outer
      WHERE sal > (SELECT AVG(sal)
                  FROM your_table inner
                  WHERE inner.key = outer.key)
Re: stuck with output [message #298199 is a reply to message #298195] Tue, 05 February 2008 05:32 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel

[Edit: Sorry pablolee didn't see your post before I reformat OP last post. For information, the whole query was on a single line]

[Updated on: Tue, 05 February 2008 05:33]

Report message to a moderator

Previous Topic: Data type
Next Topic: invalid LOB locator specified: ORA-22275 - using DBMS_LOB.LOADFROMFILE
Goto Forum:
  


Current Time: Mon Nov 04 06:31:34 CST 2024