RE: SQL Question - Sum with group by

From: Thomas L. Harleman <tharleman_at_iquest.net>
Date: Mon, 24 Jul 2000 13:04:45 -0500
Message-Id: <10568.112776@fatcity.com>

Tom Harleman
11080 Willowmere Dr.
Indianapolis, IN 46280
317-844-2884 Home
317-843-9122 Home Office

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Lucia DeMeester
Sent: Thursday, July 20, 2000 9:54 PM
To: Multiple recipients of list ORACLE-L Subject: SQL Question - Sum with group by Yes, I've done this a lot. GROUP BY the same column defs as in the SELECT. I usually format my code like this so I can cut and paste the SELECT columns into the GROUP BY clause:

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,

         to_char(o.order_date, 'MM'),
         to_char(o.order_date,'YYYY');


============================================================================

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?

TIA
Regards,
Lucia

--
Author: Lucia DeMeester
  INET: ldemeester_at_nm2.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Mon Jul 24 2000 - 13:04:45 CDT