PostgreSQL count 0 using WHEN and GROUP BY with more than one column
SELECT DISTINCT
    BUREAU.id,
    'Credit Card' as type,
    COALESCE(t1.count, 0) as count
FROM
    BUREAU
LEFT JOIN
    (SELECT 
        id,
        COUNT(*) as count
     FROM 
        BUREAU
     WHERE
        activity = 'Active'
        AND type = 'Credit Card'
     GROUP BY
        id) t1
    ON t1.id = BUREAU.id
ORDER BY
    id;



   id | type        | count
----: | :---------- | ----:
10001 | Credit Card |     0
10002 | Credit Card |     2
10003 | Credit Card |     0


db&lt;>fiddle here

Or using the conditional aggregation pointed out by a_horse_with_no_name:

SELECT
    BUREAU.id,
    'Credit Card' as type,
    COUNT(type) FILTER (WHERE type = 'Credit Card' AND activity = 'Active')
FROM
    BUREAU
GROUP BY
    BUREAU.id
ORDER BY
    BUREAU.id;


db&lt;>fiddle here