Re: Group By solution

From: Alton Ayers <altona_at_ditw.com>
Date: Thu, 17 Jun 1999 13:29:25 -0400
Message-ID: <37693074.88E17EB9@ditw.com>

Here is one solution. This assumes that the values for col_1 are 'A' thru 'J' and the values for col_2 are 'W' thru 'Z'. It'll work for this... There may be a better solution for what you are really trying to do but this maybe will give you an idea.

SELECT col_1, col_2, SUM(col_3)
FROM (SELECT * FROM table_a

        UNION ALL
        SELECT a.col_1, b.col_2, 0 col_3
          FROM (SELECT 'A' col_1 FROM dual
                UNION ALL
                SELECT 'B' col_1 FROM dual
                UNION ALL
                SELECT 'C' col_1 FROM dual
                UNION ALL
                SELECT 'D' col_1 FROM dual
                UNION ALL
                SELECT 'E' col_1 FROM dual
                UNION ALL
                SELECT 'F' col_1 FROM dual
                UNION ALL
                SELECT 'G' col_1 FROM dual
                UNION ALL
                SELECT 'H' col_1 FROM dual
                UNION ALL
                SELECT 'I' col_1 FROM dual
                UNION ALL
                SELECT 'J' col_1 FROM dual) a,
               (SELECT 'W' col_2 FROM dual
                UNION ALL
                SELECT 'X' col_2 FROM dual
                UNION ALL
                SELECT 'Y' col_2 FROM dual
                UNION ALL
                SELECT 'Z' col_2 FROM dual) b)

GROUP BY col_1, col_2
/

Alton

"M. Rothwell" wrote:

> 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 - 12:29:25 CDT