SQL: How do I return zeroes where there is nothing to aggregate across?

Question

Hi

What I would like ask is best illustrated by an example, so bear with me. Suppose I have the following table:

TypeID  Gender      Count
1       M           10
1       F           3
1       F           6  
3       M           11
3       M           8

I would like to aggregate this for every possible combination of TypeID and Gender. Where TypeID can be 1,2 or 3 and Gender can be M or F. So what I want is the following:

TypeID  Gender      SUM(Count)
1       M           10
1       F           9
2       M           0  
2       F           0
3       M           19
3       F           0

I can think of a few ways to potentially do this, but none of them seem particularly elegant to me.

Any suggestions would be greatly appreciated!

Karl

Answer 1

How about create a temporary table that will contain records that are not included on your table then do this script?

SELECT TypeID, Gender, [Count]=ISNULL(Count(*),0)  
  FROM <TABLE> 
GROUP BY TypeID, Gender

Answer 2

Use derived tables to create the rows you want to return and left join to get the counts. Use COALESCE to turn any NULL counts into zeros.

SELECT TypeIds.TypeId, Genders.Gender, COALESCE(SUM(T1.Count), 0)
FROM (
    SELECT 1 AS TypeId
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
) AS TypeIds
CROSS JOIN (
    SELECT 'M' AS Gender
    UNION ALL
    SELECT 'F'
) AS Genders
LEFT JOIN Table1 AS T1
ON TypeIds.TypeId = T1.TypeId AND Genders.Gender = T1.Gender
GROUP BY TypeIds.TypeId, Genders.Gender

It might be worth considering creating these gender and type ID tables in your database so that you can join with them. If the list of allowed type ids might change in future then you will only have to update the table instead of all the queries that have the hard-coded list of values.