CODEWITHSUNDEEP
postgresqlcrosstab
user2076663
user2076663

Reputation: 440

Returning 0 value with COUNT(*) in postgres with multiple subcategories

I'm having trouble getting COUNT(*)=0 to show up in columns. This question has been addressed on some level here: how to make this query also return rows with 0 count value?

...but I'm having trouble generalizing the solution to more than one distinct category. Here's my situation: I have 11 distinct categories of parking location and 4 distinct categories of affiliation:



    # SELECT DISTINCT parking_location FROM respondents;
     parking_location 
    ------------------
     on-street free
     city garage
     UC lot
     rpp visitor
     off-street free
     other
     nowhere
     other paid
     meter
     disabled
     rpp
    (11 rows)

    # SELECT DISTINCT affiliation FROM respondents;
     affiliation 
    -------------
     faculty
     undergrad
     grad
     staff
    (4 rows)

None of my undergrad respondents use disabled parking, so when I try to count them by parking_location, I only get 10 rows back:


    SELECT parking_location,COUNT(*) FROM respondents WHERE affiliation='undergrad' GROUP BY parking_location;
     parking_location | count 
    ------------------+-------
     on-street free   |     2
     meter            |    25
     city garage      |     5
     rpp              |    21
     nowhere          |  1012
     UC lot           |    33
     rpp visitor      |    10
     off-street free  |    10
     other            |    10
     other paid       |    12
    (10 rows)

No problem. The aforementioned link shows how to make the 0 appear:


    ths=# WITH c as (SELECT DISTINCT parking_location FROM respondents),
    ths-# r AS (SELECT affiliation, parking_location, COUNT(*) AS count FROM respondents WHERE affiliation='undergrad' GROUP BY 1,2)
    ths-# SELECT c.parking_location, COALESCE(r.count, 0) AS count FROM c
    ths-# LEFT JOIN r ON c.parking_location = r.parking_location
    ths-# ORDER BY parking_location;
     parking_location | count 
    ------------------+-------
     nowhere          |  1012
     meter            |    25
     rpp              |    21
     rpp visitor      |    10
     on-street free   |     2
     UC lot           |    33
     off-street free  |    10
     city garage      |     5
     other paid       |    12
     disabled         |     0
     other            |    10
    (11 rows)

But now I want to show the table with all affiliations, not just undergrads. Further, I want to order the resulting table first by affiliation and then by parking_location. I thought I could just eliminate the WHERE clause in the above, but then my undergrad disabled column vanishes:


    ths=#  WITH c as (SELECT DISTINCT parking_location FROM respondents), 
    ths-# r AS (SELECT affiliation, parking_location, COUNT(*) AS count FROM respondents GROUP BY affiliation,parking_location)
    ths-# SELECT r.affiliation, c.parking_location, COALESCE(r.count, 0) FROM c
    ths-# LEFT JOIN r ON c.parking_location = r.parking_location
    ths-# ORDER BY affiliation,parking_location;
     affiliation | parking_location | coalesce 
    -------------+------------------+----------
    
     staff       | city garage      |       34
     staff       | other paid       |       50
     staff       | disabled         |       18
     staff       | other            |       61
     undergrad   | nowhere          |     1012
     undergrad   | meter            |       25
     undergrad   | rpp              |       21
     undergrad   | rpp visitor      |       10
     undergrad   | on-street free   |        2
     undergrad   | UC lot           |       33
     undergrad   | off-street free  |       10
     undergrad   | city garage      |        5
     undergrad   | other paid       |       12
     undergrad   | other            |       10
     grad        | nowhere          |     1113
     grad        | meter            |       96
     grad        | rpp              |       31

Any help?

Upvotes: 1

Views: 2587

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28571

Try something like:

WITH all_parking_locations as (SELECT DISTINCT parking_location 
                               FROM respondents),

     all_affiliations as  (SELECT DISTINCT affiliation 
                           FROM respondents),

     all_counts as (SELECT affiliation, parking_location, COUNT(*) AS count 
                    FROM respondents 
                    GROUP BY affiliation, parking_location)

SELECT aa.affiliation, apl.parking_location, COALESCE(ac.count,0) as count
FROM all_affiliations aa
CROSS JOIN all_parking_locations apl
LEFT JOIN all_counts ac ON ac.affiliation = aa.affiliation
                       AND ac.parking_location = apl.parking_location
ORDER BY aa.affiliation, apl.parking_location

Upvotes: 1

Related Questions