Home » SQL & PL/SQL » SQL & PL/SQL » Can we make it in a single query?
icon4.gif  Can we make it in a single query? [message #398119] Wed, 15 April 2009 05:21 Go to next message
saini006
Messages: 8
Registered: July 2008
Location: hyderabad
Junior Member
select (select count(*)from emp) empno_cnt ,
(select count(*)from emp where job='SALESMAN' ) job_cnt,
(select count(*)from emp where sal<=2000 ) job_cnt

from dual;
Re: can we make it in a single query ............... [message #398122 is a reply to message #398119] Wed, 15 April 2009 05:32 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use sum combined with case.
Re: can we make it in a single query ............... [message #398125 is a reply to message #398119] Wed, 15 April 2009 05:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
For your information, you posted a single query, so the requirement to "make it in a single query" makes no sense. Anyway you may improve the query to use just one pass through EMP table by using CASE expression or DECODE function:
SELECT COUNT(*) empno_cnt,
       COUNT( CASE WHEN job='SALESMAN' THEN 1 END ) job_cnt,
       COUNT( CASE WHEN sal<=2000 THEN 1 END ) sal_cnt,
FROM emp;

By the way, column names in the resultset have to differ, so I changed the last one.
Re: can we make it in a single query ............... [message #398127 is a reply to message #398122] Wed, 15 April 2009 05:41 Go to previous messageGo to next message
saini006
Messages: 8
Registered: July 2008
Location: hyderabad
Junior Member
i didnt get u
Re: can we make it in a single query ............... [message #398130 is a reply to message #398125] Wed, 15 April 2009 05:44 Go to previous message
saini006
Messages: 8
Registered: July 2008
Location: hyderabad
Junior Member
thank you
Previous Topic: Date filter leads to different number of records to be displayed
Next Topic: View Related Help
Goto Forum:
  


Current Time: Sat Dec 03 10:01:06 CST 2016

Total time taken to generate the page: 0.23194 seconds