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@
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:
JOIN
s in the subquery. The correlation clause is sufficient.GROUP BY
in the outer query.COUNT(DISTINCT)
, but I'm not sure so I'm leaving it. in.FROM
clause. Always use explicit JOIN
syntax.