Including count combinations with null value in SQL-CodePudding

I have one dataset, and am trying to list all of the combinations of said dataset. However, I am unable to figure out how to include the combinations that are null. For example, Longitudinal? can be no and cohort can be 11-20, however for Region 1, there were no patients of that age in that region. How can I show a 0 for the count?

Here is the code:

SELECT "s_safe_005prod"."ig_eligi_group1"."site_name" AS "Site Name", 
       "s_safe_005prod"."ig_eligi_group1"."il_eligi_ellong" AS "Longitudinal?", 
       "s_safe_005prod"."ig_eligi_group1"."il_eligi_elcohort" AS "Cohort",
       count(*) AS "count"
FROM "s_safe_005prod"."ig_eligi_group1"
GROUP BY "s_safe_005prod"."ig_eligi_group1"."site_name", 
         "s_safe_005prod"."ig_eligi_group1"."il_eligi_ellong", 
         "s_safe_005prod"."ig_eligi_group1"."il_eligi_elcohort"
ORDER BY "s_safe_005prod"."ig_eligi_group1"."site_name", 
         "s_safe_005prod"."ig_eligi_group1"."il_eligi_ellong" ASC, 
         "s_safe_005prod"."ig_eligi_group1"."il_eligi_elcohort" ASC

CodePudding user response：

Create a cross join across the unique values from each of the three grouping fields to create a set of all possible combinations. Then left join that to the counts you have originally and coalesce null values to zero.

WITH groups AS
(
SELECT a.site_name, b.longitudinal, c.cohort
FROM (SELECT DISTINCT site_name FROM s_safe_005prod.ig_eligi_group1) a,
     (SELECT DISTINCT il_eligi_ellong AS longitudinal FROM s_safe_005prod.ig_eligi_group1) b,
     (SELECT DISTINCT il_eligi_elcohort AS cohort FROM s_safe_005prod.ig_eligi_group1) c
), 
dat AS
(
SELECT      site_name, 
            il_eligi_ellong AS longitudinal, 
            il_eligi_elcohort AS cohort, 
            count(*) AS "count"
FROM        s_safe_005prod.ig_eligi_group1
GROUP BY    site_name, 
            il_eligi_ellong, 
            il_eligi_elcohort
)
SELECT  groups.site_name,
        groups.longitudinal,
        groups.cohort,
        COALESCE(dat.[count],0) AS "count"
FROM groups 
    LEFT JOIN dat ON    groups.site_name = dat.site_name 
                    AND groups.longitudinal = dat.longitudinal
                    AND groups.cohort = dat.cohort;