Multiple Count with different value each record

Question

I have a problem with my query, please help me to solve this problem.

My Query :

SELECT D.DEPTNO, 

(SELECT COUNT(DISTINCT P.PROJNO) FROM SCHEMA.PROJECT P, SCHEMA.DEPARTMENT D WHERE P.DEPTNO = D.DEPTNO) AS PROJECT, 

(SELECT COUNT(DISTINCT E.EMPNO) FROM SCHEMA.EMPLOYEE E, SCHEMA.DEPARTMENT D WHERE E.WORKDEPT = D.DEPTNO) AS EMPLOYEE 

FROM SCHEMA.DEPARTMENT D, SCHEMA.PROJECT P, SCEHMA.EMPLOYEE E GROUP BY D.DEPTNO@

AND HERE THE RESULT :
enter image description here

but it should each row has a different result.

I must show total of project and employee each department, so i group that by deptno, but the result shown all total project and employee

Please help me guys :)

Answer 1

I think you are trying to write this query:

SELECT D.DEPTNO, 
       (SELECT COUNT(DISTINCT P.PROJNO) 
        FROM SCHEMA.PROJECT P
        WHERE P.DEPTNO = D.DEPTNO
       ) AS PROJECT, 
       (SELECT COUNT(DISTINCT E.EMPNO)
        FROM SCHEMA.EMPLOYEE E
        WHERE E.WORKDEPT = D.DEPTNO
       ) AS EMPLOYEE 
FROM SCHEMA.DEPARTMENT D;

Notes:

You don't need JOINs in the subquery. The correlation clause is sufficient.
You don't need a GROUP BY in the outer query.
You probably don't need the COUNT(DISTINCT), but I'm not sure so I'm leaving it. in.
Never use commas in the FROM clause. Always use explicit JOIN syntax.

Multiple Count with different value each record

Sign up