he;p with sql+

#1

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!

#2

On Jan 4, 10:50*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

Quote:

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!

You don't take advice well, it appears. Charles Hooper gave you a
solution to this very situation with another query (missing data) by
suggesting, rightfully so, to use an outer join and even provided an
in-line view to join to.

Your 'example' has the correct numbers? Or is this merely an
indication of HOW you'd like the output to appear? Saying it's 'all
wrong :-)' says nothing of what IS wrong or what the 'correct' output
should be.

David Fitzjarrell

#3

On Jan 4, 10:50*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

Quote:

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!

You don't take advice well, it appears. Charles Hooper gave you a
solution to this very situation with another query (missing data) by
suggesting, rightfully so, to use an outer join and even provided an
in-line view to join to.

Your 'example' has the correct numbers? Or is this merely an
indication of HOW you'd like the output to appear? Saying it's 'all
wrong :-)' says nothing of what IS wrong or what the 'correct' output
should be.

David Fitzjarrell

#4

On Jan 4, 10:50*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

Quote:

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!

You don't take advice well, it appears. Charles Hooper gave you a
solution to this very situation with another query (missing data) by
suggesting, rightfully so, to use an outer join and even provided an
in-line view to join to.

Your 'example' has the correct numbers? Or is this merely an
indication of HOW you'd like the output to appear? Saying it's 'all
wrong :-)' says nothing of what IS wrong or what the 'correct' output
should be.

David Fitzjarrell

#5

On Jan 4, 10:50*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

Quote:

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!

You don't take advice well, it appears. Charles Hooper gave you a
solution to this very situation with another query (missing data) by
suggesting, rightfully so, to use an outer join and even provided an
in-line view to join to.

Your 'example' has the correct numbers? Or is this merely an
indication of HOW you'd like the output to appear? Saying it's 'all
wrong :-)' says nothing of what IS wrong or what the 'correct' output
should be.

David Fitzjarrell

#6

David, that was not the same problem this is another one, that is not
working with the (+) David told me about;even though for the first one
it worked;
these data are real, and the way i want them to be is as the first
table, i gave in the former message, while when using the formula i
put, i am getting resultsas the second table where all the numbers are
497953000 or 0, and this is surely wrong.

**it is not that i dont take advice well, it is only that i am in the
very first steps of learning SQL, and there may be things that you
here give but i dont even understand how they work, i need more work
with SQL, i am not a kind of dumb that doesnt take advices, i m just
without experience yet. and there are many things i dont know.

Thanks again for your understanding and for your help!

#7

David, that was not the same problem this is another one, that is not
working with the (+) David told me about;even though for the first one
it worked;
these data are real, and the way i want them to be is as the first
table, i gave in the former message, while when using the formula i
put, i am getting resultsas the second table where all the numbers are
497953000 or 0, and this is surely wrong.

**it is not that i dont take advice well, it is only that i am in the
very first steps of learning SQL, and there may be things that you
here give but i dont even understand how they work, i need more work
with SQL, i am not a kind of dumb that doesnt take advices, i m just
without experience yet. and there are many things i dont know.

Thanks again for your understanding and for your help!

#8

David, that was not the same problem this is another one, that is not
working with the (+) David told me about;even though for the first one
it worked;
these data are real, and the way i want them to be is as the first
table, i gave in the former message, while when using the formula i
put, i am getting resultsas the second table where all the numbers are
497953000 or 0, and this is surely wrong.

**it is not that i dont take advice well, it is only that i am in the
very first steps of learning SQL, and there may be things that you
here give but i dont even understand how they work, i need more work
with SQL, i am not a kind of dumb that doesnt take advices, i m just
without experience yet. and there are many things i dont know.

Thanks again for your understanding and for your help!

#9

David, that was not the same problem this is another one, that is not
working with the (+) David told me about;even though for the first one
it worked;
these data are real, and the way i want them to be is as the first
table, i gave in the former message, while when using the formula i
put, i am getting resultsas the second table where all the numbers are
497953000 or 0, and this is surely wrong.

**it is not that i dont take advice well, it is only that i am in the
very first steps of learning SQL, and there may be things that you
here give but i dont even understand how they work, i need more work
with SQL, i am not a kind of dumb that doesnt take advices, i m just
without experience yet. and there are many things i dont know.

Thanks again for your understanding and for your help!

#10

On Jan 4, 1:07*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:

Quote:

David, that was not the same problem this is another one, that is not
working with the (+) David told me about;even though for the first one
it worked;
these data are real, and the way i want them to be is as the first
table, i gave in the former message, while when using the formula i
put, i am getting resultsas the second table where all the numbers are
497953000 or 0, and this is surely wrong.

**it is not that i dont take advice well, it is only that i am in the
very first steps of learning SQL, and there may be things that you
here give but i dont even understand how they work, i need more work
with SQL, i am not a kind of dumb that doesnt take advices, i m just
without experience yet. and there are many things i dont know.

Thanks again for your understanding and for your help!

As David mentioned, I attempted to answer your question here (see my
last post in that thread):
http://groups.google.com/group/comp....f960727d4854f4

I think that I see another problem with your SQL statement:
"from purinv, purch,prod,sales"
...
You are selecting rows from 4 tables...

"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')"
...
You are joining:
purinv to purch
and sales to prod

You likely need at least one more join condition in the WHERE clause
to define how the set (purinv to purch) is related to the set (sales
to prod) in order to avoid a Cartesian join between the two data
sets. Cartesian join: if there are 100 rows in the first set (purinv
to purch) and 100 rows in the second set (sales to prod), you will end
up with 10,000 result rows - in your case, this would greatly increase
the value returned by SUM, especially if you were expecting only 100
rows in the result set.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.