Better way of writing SQL ( with same output ofcourse) [message #411729] |
Mon, 06 July 2009 08:28  |
|
Hi,
I have a following table,
SQL> create table emp_project(Empid varchar2(2),prid varchar2(2),salary number(4));
Table created.
SQL> insert into emp_project values('e1','p1',1000);
1 row created.
SQL> insert into emp_project values('e1','p2',50);
1 row created.
SQL> insert into emp_project values('e2','p1',200);
1 row created.
SQL> insert into emp_project values('e2','p3',50);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp_project;
EM PR SALARY
-- -- ----------
e1 p1 1000
e1 p2 50
e2 p1 200
e2 p3 50
I need all employee information (empid,prid,salary) whos combined (total) salary is > 500.
Output needed
SQL> /
EM PR SALARY
-- -- ----------
e1 p1 1000
e1 p2 50
I wrote the following query.
select * from emp_project where empid in (select empid from (select empid,sum(salary)
from emp_project group by empid having sum(salary)>500))
/
Is there a better way of getting the same output with the same input logic
Moderators..Could you please move this to SQL/PL-SQL, I accidently created here
[Updated on: Mon, 06 July 2009 08:30] Report message to a moderator
|
|
|
|
|
|
|