Home » SQL & PL/SQL » SQL & PL/SQL » Tricky query
Tricky query [message #206219] Wed, 29 November 2006 03:54 Go to next message
Messages: 75
Registered: February 2001

Let us consider the Table 'EMP', I wanted to sum the salary based on the JOB...

In this case, i want the sum of PRESIDENT ot the sum of all the job...

My query to sum of PRESIDENT

select sum(sal) from emp where job ='PRESIDENT'

and the sum of the all the job not in PRESIDENT is

select sum(sal) from emp where job != 'PRESIDENT'

In the above query, i need to run the query 2 times.

Is there any other method to write this query so that the input parameter will handle it.


Re: Tricky query [message #206221 is a reply to message #206219] Wed, 29 November 2006 03:58 Go to previous messageGo to next message
Messages: 7880
Registered: March 2000
Senior Member
Take a look at the decode function.
Try something along these lines (not tested)
sum(decode(job, 'PRESIDENT', sal, 0) for one column
sum(decode(job, 'PRESIDENT', 0, sal) for the other column
Re: Tricky query [message #206227 is a reply to message #206219] Wed, 29 November 2006 04:03 Go to previous message
vamsi kasina
Messages: 2108
Registered: October 2003
Location: Hyderabad, India
Senior Member
If you want them in rows,
select decode(job,:p_job,job,'NOT ' || :p_job) job
  from scott.emp
group by decode(job,:p_job,job,'NOT ' || :p_job);
Previous Topic: Sort for Text
Next Topic: Loading XML into a table
Goto Forum:

Current Time: Tue Aug 22 04:22:20 CDT 2017

Total time taken to generate the page: 0.09596 seconds