| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Question - Sum with group by
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFF31D.34DE5390
Content-Type: text/plain;
charset="iso-8859-1"
This is off the cuff and untested but I think it will do what you want.
create or replace view month_qty_sum_by_sku as select sku, year, month, sum(qty) from ( select i.sku,
to_char(o.order_date,'YYYY') year,
to_char(o.order_date, 'MM') month,
i.quantity as qty
from items i,
orders o
-----Original Message-----
From: Lucia DeMeester [mailto:ldemeester_at_nm2.com]
Sent: Thursday, July 20, 2000 9:54 PM
To: Multiple recipients of list ORACLE-L
Subject: 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?
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 ListsReceived on Fri Jul 21 2000 - 08:56:44 CDT
--------------------------------------------------------------------
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 also send the HELP command for other information (like subscribing).
------_=_NextPart_001_01BFF31D.34DE5390
Content-Type: text/html; charset="iso-8859-1" <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2650.12"> <TITLE>RE: SQL Question - Sum with group by</TITLE> </HEAD> <BODY> <P><FONT SIZE=2>This is off the cuff and untested but I think it will do what you want.</FONT> </P> <P><FONT SIZE=2>create or replace view month_qty_sum_by_sku as</FONT> <BR><FONT SIZE=2>select sku, year, month, sum(qty) from (</FONT> <BR><FONT SIZE=2>select i.sku, </FONT> <BR><FONT SIZE=2> to_char(o.order_date,'YYYY') year,</FONT> <BR><FONT SIZE=2> to_char(o.order_date, 'MM') month, </FONT> <BR><FONT SIZE=2> i.quantity as qty</FONT> <BR><FONT SIZE=2>from items i, </FONT> <BR><FONT SIZE=2> orders o </FONT> <BR><FONT SIZE=2>where i.order_id = o.order_id </FONT> <BR><FONT SIZE=2>and (o.status=5 or o.status=7 or (o.status=16 and i.printed=1)) </FONT> <BR><FONT SIZE=2>and (o.store_id = 'V') )</FONT> <BR><FONT SIZE=2>group by sku, year, month;</FONT> </P> <P><FONT SIZE=2>-----Original Message-----</FONT> <BR><FONT SIZE=2>From: Lucia DeMeester [<A HREF="mailto:ldemeester_at_nm2.com">mailto:ldemeester_at_nm2.com</A>]</FONT> <BR><FONT SIZE=2>Sent: Thursday, July 20, 2000 9:54 PM</FONT> <BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=2>Subject: SQL Question - Sum with group by</FONT> </P> <BR> <P><FONT SIZE=2>I have trouble to create a view that will sum a quantity field from a joined</FONT> <BR><FONT SIZE=2>table by sku and year and month (YYYYMM). But also display the sku, summed</FONT> <BR><FONT SIZE=2>quantity, MM, and YYYY. The ouput of the view is going to write to an</FONT> <BR><FONT SIZE=2>inventory table. Sample output: VA013000, 05, 2000, 50 One record per</FONT> <BR><FONT SIZE=2>each YYYYMM for each sku.)</FONT> </P> <P><FONT SIZE=2>If I use the following script, it generates detail quantity for each sku.</FONT> <BR><FONT SIZE=2>The quantity is not summaried by the YYYYMM. If I use group by i.sku,</FONT> <BR><FONT SIZE=2>to_char(o.order_date,'YYYYMM') then I got error.</FONT> </P> <P><FONT SIZE=2>create or replace view month_qty_sum_by_sku as</FONT> <BR><FONT SIZE=2>select i.sku, to_char(o.order_date, 'MM') as month, to_char(o.order_date,</FONT> <BR><FONT SIZE=2>'YYYY') as year</FONT> <BR><FONT SIZE=2>,sum(i.quantity) as qty</FONT> <BR><FONT SIZE=2>from items i, orders o where</FONT> <BR><FONT SIZE=2>i.order_id = o.order_id and (o.status=5 or o.status=7 or (o.status=16 and</FONT> <BR><FONT SIZE=2>i.printed=1)) and</FONT> <BR><FONT SIZE=2> (o.store_id = 'V')</FONT> <BR><FONT SIZE=2>group by i.sku, o.order_date;</FONT> </P> <P><FONT SIZE=2>I did try to define a new yymm column and try to group by sku and yymm</FONT> <BR><FONT SIZE=2>column and it did not work.</FONT> </P> <P><FONT SIZE=2>Any one has done this sort of SQL?</FONT> </P> <BR> <P><FONT SIZE=2>TIA</FONT> <BR><FONT SIZE=2>Regards,</FONT> <BR><FONT SIZE=2>Lucia</FONT> </P> <P><FONT SIZE=2>-- </FONT> <BR><FONT SIZE=2>Author: Lucia DeMeester</FONT> <BR><FONT SIZE=2> INET: ldemeester_at_nm2.com</FONT> </P> <P><FONT SIZE=2>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT SIZE=2>--------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT SIZE=2>(or the name of mailing list you want to be removed from). You may</FONT> <BR><FONT SIZE=2>also send the HELP command for other information (like subscribing).</FONT>
 |
 |