stuck with output [message #298016] |
Mon, 04 February 2008 10:46 |
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 |
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 |
|
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 |
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 |
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 #298198 is a reply to message #298195] |
Tue, 05 February 2008 05:30 |
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 |
|
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
|
|
|