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!
