Including records in a query result that have no values

[holmm]

Feel free to critise/comment. I've been slightly thrown in at the deep end!

I'm building a query to count the number of test results that are unapproved, Out of spec, null etc per manufacturing site by Date(current month, last 12 months or all others).

I have the query returning results correctly but the only problem is it won't return 0's for the manufacturng sites that have none of the above.

Query:

SELECT  CDA.MANUFACTURING_SITES.MANUFACTURING_SITE_NAME, 
    -- CDA.TESTS.TEST_DATE,
    COUNT(CDA.RESULTS.RESULT_ID),
    COUNT( CASE WHEN CDA.RESULTS.IS_APPROVED = 0 THEN 1 END) UNAPPROVED,
    COUNT( CASE WHEN  CDA.RESULTS.ACTUAL_RESULT IS NULL THEN 1 END) NO_RESULT,
    COUNT(CASE WHEN CDA.RESULTS.ACTUAL_RESULT IS NOT NULL AND CDA.RESULTS.ACTUAL_RESULT > CDA.RESULTS.RESULT_REFERENCE_LIMIT THEN 1 END) OUT_OF_SPEC--,
FROM 
    CDA.MANUFACTURING_SITES,
    CDA.BATCHES,
    CDA.TESTS,
    CDA.RESULTS
WHERE
    CDA.MANUFACTURING_SITES.MANUFACTURING_SITE_ID = CDA.BATCHES.MANUFACTURING_SITE_ID (+)
    AND CDA.BATCHES.BATCH_ID = CDA.TESTS.BATCH_ID (+)
    AND CDA.TESTS.TEST_ID =  CDA.RESULTS.TEST_ID (+)
    AND TO_CHAR(CDA.TESTS.TEST_DATE,'MM-YYYY') = TO_CHAR(SYSDATE, 'MM-YYYY') --CURRENT MONTH
GROUP BY CDA.MANUFACTURING_SITES.MANUFACTURING_SITE_NAME--, CDA.TESTS.TEST_DATE
ORDER BY CDA.MANUFACTURING_SITES.MANUFACTURING_SITE_NAME
 
 

output:

MANUFACTURING_SITE_NAME: Ireland
COUNT(CDA.RESULTS.RESULT_ID): 8
UNAPPROVED: 8
NO_RESULT: 7
OUT_OF_SPEC: 1

Is there a way I can get the the other mamufacturing sites returned with 0's as their results returned?

Any input would be greatly appreciated. It you have any questions give me a shout.

Thanks

[debasisdas]

I think you are getting what you have asked for in the query using all those joins.