COUNT returning NULL, should return 0

Question

I have a simple query for the table:

Person id Organization id  employee_nam age  busines_group_id
123       Zuyo         10     John      30   81
2457      Zuyo         10     Geet      69   81
56        Ghiya        12     paul      20   81 
          frei         13                    81

SELECT
    COUNT(DISTINCT ped.person_id)
FROM
    per_emp_detail ped
WHERE
    ped.business_group_id = 81
    AND
    ped.id = NVL(p_org_id, ped.organization_id);

SELECT
    NVL(COUNT(DISTINCT ped.person_id), 0)
FROM
    per_emp_detail ped
WHERE
    ped.business_group_id = 81
    AND
    ped.id = NVL(p_org_id, ped.organization_id);

p_org_id is the parameter which I am passing which can be 10, 12, or 13. COUNT returns 2 for id 10. 1 for id 12. but is returning NULL for id 13. I want 0 to be returned in this case.

NVL and CASE are also not working.

Answer 1

try MAX:

nvl(max(count(DISTINCT ped.person_id)),0)