SQL Question - Sum with group by

I have trouble to create a view that will sum a quantity field from a joined table by sku and year and month (YYYYMM). But also display the sku, summed quantity, MM, and YYYY. The ouput of the view is going to write to an inventory table. Sample output: VA013000, 05, 2000, 50 One record per each YYYYMM for each sku.)

If I use the following script, it generates detail quantity for each sku. The quantity is not summaried by the YYYYMM. If I use group by i.sku, to_char(o.order_date,'YYYYMM') then I got error.

create or replace view month_qty_sum_by_sku as select i.sku, to_char(o.order_date, 'MM') as month, to_char(o.order_date, 'YYYY') as year
,sum(i.quantity) as qty
from items i, orders o where
i.order_id = o.order_id and (o.status=5 or o.status=7 or (o.status=16 and i.printed=1)) and
 (o.store_id = 'V')
group by i.sku, o.order_date;

I did try to define a new yymm column and try to group by sku and yymm column and it did not work.

Any one has done this sort of SQL? Received on Thu Jul 20 2000 - 20:41:32 CDT