sum in table then subtract with different table

Question

after two week try i couldn't write this query please help me

NOTE
  TABLE 1 sum(quantity_box) group by id_p and id_w
  TABLE 2 sum(quantity_box) group by id_p and id_w
then
  TABLE1.sum(quantity_box)- TABLE2.sum(quantity_box) where id_p = id_p and id_w = id_w

table 1 ) wherehouseproduct_add id = id_w

id | name
10 | warehouse1
20 | warehouse2

table2) wherehouse_products

id | id_w |id_p |quantity_box
1  |  10  | 2   |  10  
2  |  10  | 2   |  50  
3  |  20  | 3   |  100
4  |  20  | 1   |  20
5  |  20  | 1   |  10
6  |  10  | 1   |  10
7  |  10  | 3   |  10

table3) wherehouse_products_sell

 id | id_w |id_p |quantity_box
 1  |  10  | 2   |  50  
 2  |  20  | 3   |  30
 3  |  20  | 1   |  20

table4) products

id_p | product_name
1    | snack
2    | watebottle
3    | headphone

i want to output like

id_w | id_p  | product_name| total_quantity_box
10   | 1     | snack       |10
10   | 2     | watebottle  |10   
10   | 3     | headphone   |10

and

  id_w | id_p  | product_name| total_quantity_box
    20   | 1     | snack       |10
    20   | 2     | watebottle  |10   
    20   | 3     | headphone   |70

for now i used

select wp.id_w,
wp.id_p,
wp.quantity - wps.quantity total_quantity_box
from
(select id_w,id_p,sum(quantity_box) quantity from warehouse_products group by id_w,id_p) wp
join
(select id_w,id_p,sum(quantity_box) quantity from warehouse_products_sell group by id_w,id_p) wps
using(id_w,id_p)

**after two week search**. If you can't find it by searching, you need to write it yourself. Programming is not a copy-paste job, it's a creative process using your knowledge of the language. — Barmar, Sep 29 '17 at 16:24
You need to use `LEFT JOIN`, so that a product with no sells will be included in the result. And then use `IFNULL(wps.quantity, 0)` to treat that as zero sales. — Barmar, Sep 29 '17 at 16:32

Barmar · Accepted Answer · 2017-09-29T16:37:38.417

1

You need to use LEFT JOIN so you'll get results for products that aren't in wherehouse_products_sell.

select wp.id_w, wp.id_p, p.product_name,
    wp.quantity - IFNULL(wps.quantity, 0) total_quantity_box
from
    (select id_w,id_p,sum(quantity_box) quantity from warehouse_products group by id_w,id_p) wp
left join
    (select id_w,id_p,sum(quantity_box) quantity from warehouse_products_sell group by id_w,id_p) wps
using(id_w,id_p)
join products AS p ON wp.id_p = p.id_p

edited Sep 29 '17 at 16:37

answered Sep 29 '17 at 16:34

Barmar

741,623
53
500
612

thats work now and how can i add product name in my output shd i use 2nd left JOIN to product table ? – user8663822 Sep 29 '17 at 16:36
You can use a regular join for that, since product ID should always have a matching row there. – Barmar Sep 29 '17 at 16:36
one last think in this query it output all id_w, can i use Where id_w = 20 . for select only id_w that i need ? – user8663822 Sep 29 '17 at 16:40
Yes, you can do that. It would be best to put that into the subqueries. – Barmar Sep 29 '17 at 16:45
any idea how can i create view in this query . i used CREATE VIEW totalQuantiy AS . ......(query) it said View's SELECT contains a subquery in the FROM clause – user8663822 Sep 29 '17 at 16:54
See https://stackoverflow.com/questions/206062/mysql-view-with-subquery-in-the-from-clause-limitation?noredirect=1&lq=1. The solution is to create views for each of the subqueries, then join those views. – Barmar Sep 29 '17 at 18:28

sum in table then subtract with different table

1 Answers1