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

Posted by Karl on Stack Overflow See other posts from Stack Overflow or by Karl
Published on 2010-03-15T07:17:36Z Indexed on 2010/03/15 7:19 UTC
Read the original article Hit count: 422

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about aggregate