CODEWITHSUNDEEP
sql
Wtrfilesx
Wtrfilesx

Reputation: 11

Include zero counts when grouping by multiple columns

I have a table (TCAP) containing the gender (2 categories), race/ethnicity (3 categories), and height (integer in inches) for multiple individuals. For example:

GND     RCE      HGT
1           3           65
1           2           72
2           1           62
1           2           68
2           1           65
2           2           64
1           3           69
1           1           70

I want to get a count of the number of individuals in each possible gender and race/ethnicity combination. When I group by GND and RCE, however, it doesn't show zero counts. I've tried the following code:

SELECT
   GND,
   RCE,
   COUNT(*) TotalRecords
FROM TCAP

GROUP BY GND, RCE;

This gives me: 

GND   RCE   TotalRecords
1         1         1
1         2         2
1         3         2
2         1         2
2         2         1

I want it to show all possible combinations though. In other words, even though there are no individuals with a gender of 1 and race/ethnicity of 3 in the table, I want that to display as a zero count. So, like this: 

GND   RCE   TotalRecords
1         1         1
1         2         2
1         3         2
2         1         2
2         2         1
2         3         0

I've looked at the responses to similar questions, but they are based on a single group, resolved using an outer join with a table that has all possible values. Would I use a similar process here? Would I create a single table that has all 6 combinations of GND and RCE to join on? Is there another way to accomplish this, especially if the number of combinations increases (for example, 1 group with 5 values and 1 group with 10 values)?

Any help would be much appreciated! Thanks!

Upvotes: 1

Views: 1321

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Use a cross join to generate the rows and a left join to bring in the results -- with a final group by:

select g.gnd, r.rce, count(t.gnd) as cnt
from (select distinct gnd from tcap) g cross join
     (select distinct rce from tcap) r left join
     tcap t
     on t.gnd = g.gnd and t.rce = r.rce
group by g.gnd, r.rce;

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

You can try to use CROSS JOIN make for GND,RCE columns then do OUTER JOIN base on it.

Query #1

SELECT t1.GND,t1.RCE,COUNT(t3.GND) TotalRecords
FROM (
    SELECT GND,RCE
    FROM (
      SELECT DISTINCT GND
      FROM TCAP
    ) t1 CROSS JOIN
    (
      SELECT DISTINCT RCE FROM TCAP
    )  t2
) t1
LEFT JOIN TCAP t3 ON t3.GND = t1.GND and t3.RCE = t1.RCE
group by  t1.GND,t1.RCE;

| GND | RCE | TotalRecords |
| --- | --- | ------------ |
| 1   | 1   | 1            |
| 1   | 2   | 2            |
| 1   | 3   | 2            |
| 2   | 1   | 2            |
| 2   | 2   | 1            |
| 2   | 3   | 0            |

View on DB Fiddle

Upvotes: 1

Related Questions