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

10001 has no Credit Card type.

McNets
– McNets

2019-01-24 18:00:44 +00:00
Commented Jan 24, 2019 at 18:00 — McNets
– McNets, Commented Jan 24, 2019 at 18:00

McNets · Accepted Answer · 2019-01-24 18:12:18Z

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

edited Jan 24, 2019 at 18:12

answered Jan 24, 2019 at 18:05

McNets

24k11 gold badges52 silver badges91 bronze badges

I'm glad to help.

McNets
– McNets

2019-01-24 18:28:34 +00:00
Commented Jan 24, 2019 at 18:28

Add a comment |

Stack Exchange Network

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

1 Answer 1

Your Answer

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions