Can we make it in a single query? [message #398119] |
Wed, 15 April 2009 05:21  |
saini006
Messages: 9 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 #398125 is a reply to message #398119] |
Wed, 15 April 2009 05:37   |
flyboy
Messages: 1903 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.
|
|
|
|
|