What about :
SELECT
PROJECT."PRIMARY_PROJECT_CODE" AS COL0,
COUNT(PHYSICAL_COPY."ID") AS COL1
FROM
PROJECT
LEFT JOIN PROJECT_CODE_DESCRIPTOR ON PROJECT_CODE_DESCRIPTOR."PROJECT_ID"
= PROJECT."ID"
JOIN COPY_CLASSIFICATION ON COPY_CLASSIFICATION."CLASSIFICATION_CODE" =
PROJECT_CODE_DESCRIPTOR."PROJECT_CODE"
JOIN COPY ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE
( (PROJECT_CODE_DESCRIPTOR."PROJECT_ID" IS NOT NULL) and
(PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED') and
(PHYSICAL_COPY."IS_MARKED_DELETED" = 0) and
(PHYSICAL_COPY."IS_RECYCLED" = 0) )
GROUP BY
PROJECT."PRIMARY_PROJECT_CODE"
Regards,
Arno
