| simple but complex query [message #277584] |
Tue, 30 October 2007 12:40  |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
Hi,
I have a table:
create or replace table sample
(empno number,
sal number,
deptno varchar2(10));
insert into sample values (1, 300, 'HR');
insert into sample values (2, 2000, 'OPS');
insert into sample values (3, 1200, 'HR');
insert into sample values (4, 220, 'Admin');
insert into sample values (5, 1500, 'Admin');
insert into sample values (6, 110, 'HR');
insert into sample values (7, 800, 'OPS');
insert into sample values (8, 3000, 'OPS');
insert into sample values (9, 4000, 'HR');
insert into sample values (10, 560, 'Admin');
Now, I need to get the average salary per department and the employee in that department who has salary above or equal to that average. Without using analytical functions, please help me with this SQL.
Thanks in advance..
|
|
|
|
|
|
|
|
| Re: simple but complex query [message #277670 is a reply to message #277593] |
Wed, 31 October 2007 02:13  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
I'll give you some pointers:
1 .To calculate the average salary, Oracle has a function AVG (AVeraGe).
SELECT AVG(salary) avg_sal
FROM yourtable
2. To get this for each department, use GROUP BY
3. To get the employees with a salary higher than the average for his department, you can use a correlated subquery. A subquery in the where clause that references a table in the from clause.
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
