Home » SQL & PL/SQL » SQL & PL/SQL » Better way of writing SQL ( with same output ofcourse) (Oracle 10g,Win XP)
Better way of writing SQL ( with same output ofcourse) [message #411729] Mon, 06 July 2009 08:28 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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

Re: Better way of writing SQL ( with same output ofcourse) [message #411741 is a reply to message #411729] Mon, 06 July 2009 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please format your query (See SQL Formatter) in a readable way.

SQL> with 
  2    data as (
  3      select empid, prid, salary,
  4             sum(salary) over (partition by empid) sumsal
  5      from emp_project
  6    )
  7  select empid, prid, salary
  8  from data
  9  where sumsal > 500
 10  /
EM PR     SALARY
-- -- ----------
e1 p1       1000
e1 p2         50

2 rows selected.

Regards
Michel
Re: Better way of writing SQL ( with same output ofcourse) [message #411744 is a reply to message #411729] Mon, 06 July 2009 09:13 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey Michel, Thanks for the query..

I was just practising it..Can we achieve this without a Analytical Function (Actually was asked in Interview). I had given the query using Analytical function, And then gave the query which I posted here...
Re: Better way of writing SQL ( with same output ofcourse) [message #411751 is a reply to message #411744] Mon, 06 July 2009 10:02 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Here one more then

select *
from (
		select Empid,prid,salary
		from emp_project
	) natural join (	
		select Empid
		from emp_project
		group by Empid
		having sum(salary) > 500
	)


Bye Alessandro

[Updated on: Mon, 06 July 2009 10:03]

Report message to a moderator

Re: Better way of writing SQL ( with same output ofcourse) [message #411817 is a reply to message #411729] Tue, 07 July 2009 00:26 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SELECT *
  FROM emp_project
 WHERE empid IN (SELECT   empid
                   FROM   emp_project
                 GROUP BY empid
                   HAVING SUM (salary) > 500)
Previous Topic: Monitoring transactions
Next Topic: Find the Invalid Entry in the table
Goto Forum:
  


Current Time: Tue Dec 06 02:25:29 CST 2016

Total time taken to generate the page: 0.16498 seconds