Re: group by problem in SQL

Hi,

Try selecting SUM(NVL(TOTAL_CAPACITY,0)) instead of SUM(TOTAL_CAPACITY)

Good luck.

Raghu

>
>Hey folks - Here's a piece of SQL code I'm working on:
>SELECT TRUNC(a.week, 'mm') AS Month, sourceplant, sum(demand),
>sum(total_capac
>ity)
>FROM so_demand a, plant_capacity b
>WHERE TRUNC(b.week, 'mm') = TRUNC(a.week, 'mm')
> AND a.sourceplant = b.plant
> AND a.demand >= b.total_capacity
>GROUP BY TRUNC(a.week, 'mm'), sourceplant;
>It seems to do the sum of demand fine, but the second sum(of capacity)
>doesn't work. Can I do this without creating a separate view off of the
>plant_capacity table? Thanks in advance - Jim
>

--

                         \|||/
                        ( O-O )
-------------------.ooo0--(_)-0ooo.----------------------------------------

Raghavendra A.                Internet : rags_at_hpiso52.india.external.hp.com
Consultant                    Fax      : +91-80-2200196
HP India Software Operations  Phone    : +91-80-2251554 Extn : 413 (Off)
Bangalore, INDIA                         +91-80-6680198 (Res)
                     .oooO
---------------------(   )----Oooo.----------------------------------------
                      \ (     (   )
                       \_)     ) /
                              (_/