Group By solution

From: M. Rothwell <marothwell_at_my-deja.com>
Date: Thu, 17 Jun 1999 15:37:05 GMT
Message-ID: <7kb4mv$m5i$1@nnrp1.deja.com>

I'm trying to get some SQL that will return a record for a group even if the record does not exist.

select col_1, col_2, sum( col_3 )
from table_a
group by col_1, col_2

col_1 col_2 sum( col_3 )
------ ------ ------------

A        X                 10
A        Y                 20
A        Z                 15

This works fine when I have all values for col_1 and col_2, but if I am missing a record that has col_2 = 'Y' then my group by will only return 2 rows. I want to return all possible rows and have the sum( col_3 ) = 0.

col_1 col_2 sum( col_3 )
------ ------ ------------

A        X                 10
A        Y                  0
A        Z                 15

instead of

col_1 col_2 sum( col_3 )
------ ------ ------------

A        X                 10
A        Z                 15

I only have 10 values for col_1 and 4 possible values for col_2.

Thanks for any suggestions.

Michael

mrothwell_at_jps.net

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 17 1999 - 10:37:05 CDT