PostgreSQL count 0 using WHEN and GROUP BY with more than one column

Question

I am trying to make a new table that shows the number of Active Credit Cards each customer has in my dataset using:

SELECT 
 sk_id_curr,
 credit_type,
 credit_active, 
 COUNT(credit_type) 
FROM 
 BUREAU
WHERE 
 credit_active='Active' AND credit_type='Credit card'
GROUP BY 
 sk_id_curr,
 credit_type,
 credit_active

The problem is that it removes ID's where there are no Active Credit Cards. I want it to return ID's with no credit cards with count 0. How would I do this?

Ex:

Original Data

-------------------------------------------------
id type activity
--------------------------------------------------
10001 Consumer Credit Active
10002 Credit Card Active
10002 Credit Card Active
10003 Credit Card Closed

Current result

-----------------------------------------------------------------------------
id, type, activity, count
------------------------------------------------------------------------------
10002 Credit Card Active 2

desired result

-----------------------------------------------------------------------------
id type activity count
------------------------------------------------------------------------------
10001 Credit Card Active 0
10002 Credit Card Active 2
10003 Credit Card Active 0

• McNets: 10001 has no Credit Card type.

Answer 1

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<>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<>fiddle here

• McNets: I'm glad to help.