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

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'

Question
--------
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.


regards
YRAM


Re: Tricky query [message #206221 is a reply to message #206219] Wed, 29 November 2006 03:58 Go to previous messageGo to next message
Frank
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: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
If you want them in rows,
select decode(job,:p_job,job,'NOT ' || :p_job) job
      ,sum(sal) 
  from scott.emp
group by decode(job,:p_job,job,'NOT ' || :p_job);
By
Vamsi
Previous Topic: how to find duplicate record ?
Next Topic: email attachment >32k in 10g
Goto Forum:
  


Current Time: Mon Dec 05 18:55:44 CST 2016

Total time taken to generate the page: 0.12497 seconds