he;p with sql+

From: Totti <saliba.toufic.george_at_gmail.com>
Date: Fri, 4 Jan 2008 08:50:49 -0800 (PST)
Message-ID: <02ec4c90-a6c9-487e-a661-46994276e993@d4g2000prg.googlegroups.com>

hi all i want to generate the total sales of the 3 most sold products of a firm, i ll try to write it here
as follows from more than one table but the result i am getting is awful, and has nothing to do with the real thing; take a look please and tell me what and where the problem might be:

the result i intend to get is this :

Month Year SP-20.1      SP-20.3      SP-20.4
-----       ----        ------------    ------------    ------------
   01 2006   24,700,000      880,000    1,476,000
   02 2006   19,240,000      660,000    2,132,000
   03 2006    8,320,000      330,000    2,296,000
   01 2007   24,180,000      770,000      902,000
==================================================

i am using the following formula:

SELECT TO_CHAR(purinv.Inv_Date,'mm')
MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR, SUM(DECODE(PURCH.PROD_CODE,'SP-20.2',sales.QTY*prod.unit_price,0)) AS
"SP-20.2",

SUM(DECODE(PURCH.PROD_CODE,'SP-20.1',sales.QTY*prod.unit_price,0))AS
"SP-20.1" ,

SUM(DECODE(PURCH.PROD_CODE,'SP-20.6',sales.QTY*prod.unit_price,0))AS
"SP-20.6"

from purinv, purch,prod,sales
where purinv.Code = purch.Inv_Code and sales.prod_code = prod.code and purch.Prod_Code in ('SP-20.2', 'SP-20.1', 'SP-20.6') group by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY') order by TO_CHAR(purinv.Inv_Date,'YYYY'),TO_CHAR(purinv.Inv_Date,'mm')

the result i am getting has 2 wrong things, first of all it is all wrong :-), and second, when a month let us say had not any sales of those 3 product, it omits the whole thing, which something i dont want, for instance since from the 4th of 2006 untill the 12th there has been no sales of this product, but the formula doesnt show 0 as i wanted , instead it throws them out.

concerning the result i am getting here it is:

MO YEAR SP-20.2 SP-20.1 SP-20.6 -- ---- ---------- ---------- ---------- 01 2006 497953000 497953000 497953000

03 2006          0          0              497953000
04 2007          0       497953000          0
05 2007  497953000          0        497953000
07 2007          0   497953000        497953000
08 2007          0   497953000        497953000
09 2007  497953000  497953000  497953000
11 2007          0          0              497953000

 this is crazy i mean , please if you can help me correct the formula! Received on Fri Jan 04 2008 - 10:50:49 CST