combining query

[Tee]

How can I combine these queries:
1.select count(ename), select count(job) from emp where deptno in ('10')

2.select count(ename), select count(job) from emp where deptno in ('20')

3.select count(ename), select count(job) from emp where deptno in ('30')

4.select count(ename), select count(job) from emp where deptno in ('50')

5.select count(ename), select count(job) from emp where deptno in ('60')

QUESTION: How do I combine all of these queries into one instead of having to type all five queries all the time.

I want to be able to see the totals for 10,20,30,40,50,60 as well so those who think this
select count(ename), select count(job) from emp where deptno in ('10','20','30','40','50','60')

will solve the problem, it will not. It will not because, it will just give me one single summation for all deptno. I want to be able to see the tally for each deptno


May be if I can get the output to look something like this
count(ename) count(job) 10 20 30 40 50
-------------- ------------ --- --- --- --- ---
14 12 2 5 4 3 2



TEE

[dsuva]

Use the UNION Operator betwen the different SQL Queries..and you will get desired result

[pramathesh]

Hi Tee,

Try out the following query, u may get the result.

/*

SELECT COUNT(E.ENAME),COUNT(DISTINCT E.JOB),D.D10,C.D20,B.D30,A.D40 FROM EMP E,
(SELECT COUNT(*) D10 FROM EMP WHERE DEPTNO=10 ) D,
(SELECT COUNT(*) D20 FROM EMP WHERE DEPTNO=20 ) C,
(SELECT COUNT(*) D30 FROM EMP WHERE DEPTNO=30 ) B,
(SELECT COUNT(*) D40 FROM EMP WHERE DEPTNO=40 ) A
GROUP BY D10,D20,D30,D40

*/

Best of luck

[bhavanikri]

select count(ename), count(job), deptno from emp group by deptno;

[sln81]

Try it this way.

select count(ename), count(job)
from emp
where deptno in ('10','20','30','40','50','60')
group by deptno