0

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
  • 10001 has no Credit Card type. – McNets Jan 24 at 18:00
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

  • I'm glad to help. – McNets Jan 24 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.