Re: group by problem in SQL

Jim,

        The problem could be because of the group by clause where u are using the  column (week) from table a but the grouping is not done for the column ( week  ) in table b for which u need the sum (total_capacity). Change a.week to  b.weekin the group by clause , if you get correct result for  sum(total_capacity) and wrong for sum(demand) that will prove the point.

> 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
>
Received on Thu Feb 08 1996 - 06:08:23 CST