PostgreSQL – Using WHEN and GROUP BY to Count Zero

postgresql

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

Best Answer

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

Related Question