join 2 tables in SUM IF statement-mysql

[4846]

I am trying to join 2 tables with in a SUM IF statement. the code is as under:

SELECT prod_code, description, description1, quant,
SUM(IF(newsales.location = '05-P', newsales.quant,''))P,
SUM(IF(newsales.location = '10-L', newsales.quant,'')) L,
SUM(IF(newsales.location = '12-M', newsales.quant,'')) M,
SUM(IF(newsales.location = '11-BI', newsales.quant,'')) Bi,
SUM(IF(newsales.location = '14-BR', newsales.quant,'')) Br
from newsales, stock
where newsales.prod_code = stock.prod
group by prod_code

But it gives an incorrect output, but if I romove the join statement, it gives accurate resuly.

How to join the 2 tables ie newsles and stock so that the output remains right.

Any help is appreciated.

regards

 

[swampBoogie]

In which sense is the result incorrect?

Could you provide a description of the tables, some sample data and your expected result and how this compares to the result of your current query.

 

[4846]

Thanks for your reply and the first table with the name of newsales with sample data is as under

prod_code description quant location year record_num ACC001 Hair 6 12-M 2003 3842
ACC401 Velvet 27 05-P 2003 178
ACC403 Necklace 20 10-L 2003 1341
ACC403 Necklace 21 05-P 2003 1342
ACC403 Necklace 29 12-M 2003 1345
and so on


where record_num is autoincrement. The 2nd table is as under with the name of stock

prod description1 store reorder freestock physicalstock ACC401 Velvet 05-P 10 12 12
ACC401 Velvet 10-L 6 6 6
ACC401 Velvet 11-B 4 5 5
ACC401 Velvet 12-M 4 4 4
ACC403 Necklace 05-P 17 15 15
ACC403 Necklace 10-L 24 36 36


If I don`t put the join statement and use only one table ie

from newsales group by prod_code

the out put shows the correct quant from table newsales ie 6 27 20 and so on

but if use join and two table ie
from newsales, stock
where newsales.prod_code = stock.prod

then the output shows much higher ie some 120 260 etc which is not correct and this value is not any where in any table.


where as what I want is to add another column from 2nd table called stock, and at the same time the values for quant should be correct, so I am trying to join two tables.

Ideally I want the following output:-

LA LB MA MB BA BB BaA BaB


Where LA = Quant (from table newsales)where location = 05-L
LB = reorder(from table stock)where location = 05-L
MA = Quant (from table newsales) where location = 12-M
MB = reorder (from table stock) where location = 12-M
BA = Quant (from table newsales)where location = 11-B
BB = reorder(from table stock) and location = 11-B

and so on


I am really thankful to you.

Kind regards

 

[swampBoogie]

As you didn't have any join condition on the store/location columns you end up with multiple rows for each product and location. This is the cause of the high values for the sum function. By using an outer join you will get rows even though there is no corresponding entry in the newsales table.

Another thing is that when using group by all non-aggregated columns in the select list must be included in the group by clause. Mysql is the only DBMS I know of that allows that you deviate from this which causes a lot of grief as you get all sorts of unpredictable behaviour.

SELECT   prod_code, description, description1,
   sum(case when stock.store = '05-P'  then newsales.quant else 0 end) P,
   SUM(case when stock.store = '10-L'  then newsales.quant else 0 end) L,
   SUM(case when stock.store = '12-M'  then newsales.quant else 0 end) M,
   SUM(case when stock.store = '11-BI' then newsales.quant else 0 end) Bi,
   SUM(case when stock.store = '14-BR' then newsales.quant else 0 end) Br,
   sum(case when stock.store = '05-P'  then stock.reorder  else 0 end) ReorderP,
   SUM(case when stock.store = '10-L'  then stock.reorder  else 0 end) ReorderL,
   SUM(case when stock.store = '12-M'  then stock.reorder  else 0 end) ReorderM,
   SUM(case when stock.store = '11-BI' then stock.reorder  else 0 end) ReorderBi,
   SUM(case when stock.store = '14-BR' then stock.reorder  else 0 end) ReorderBr,
from stock left join newsales
on newsales.prod_code = stock.prod
   newsales.location = stock.store
group by prod_code, description, description1

 

[4846]

Dear swampBoogie

Thanks a lot for your cooperation. It has worked.

Regards