Fathin Naufal Fathin Naufal - 4 months ago 7
SQL Question

Multiple Count with different value each record

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

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.