Mysql query for sum two table data by product wise

[nishita_1]

Dear sir, Please help me about below query. here is two table sale and purchase. i want product wise result in output table like below picture. please help me

[Dani]

I’m on my phone now so I can’t search easily, but didn’t you just ask this question two days ago?

[nishita_1]

MR Pritaeas provide below code its works but item 4 did't show in output because sale raw is empty. but i want all data show like output table product wise. please help me my project submit earlyer.

SELECT * 
FROM (
    SELECT product_id, SUM(quantity) AS purchase
    FROM purchase 
    GROUP BY product_id
) P
JOIN (
    SELECT product_id, SUM(quantity) AS sale
    FROM sale
    GROUP BY product_id
) S ON S.product_id = P.product_id

[AndrisP]

I recommend use join instead of subquery if possible because join works faster. I think you have another table e.g. products which contains all product id. Select product_id from products and left join sale and purchase. Where clause exclude null values e.g.

select
    p.product_id
    ,sum(s.quantity) sale
    ,sum(c.quantity) purchase
from products p
left join purchase c on c.product_id = p.product_id
left join sale s on s.product_id = p.product_id
where c.quantity is not null or s.quantity is not null
group by p.product_id;

also may add rollup to get total sales and total purchases

select
    coalesce(p.product_id,'Total')
    ,sum(s.quantity) sale
    ,sum(c.quantity) purchase
from products p
left join purchase c on c.product_id = p.product_id
left join sale s on s.product_id = p.product_id
where c.quantity is not null or s.quantity is not null
group by p.product_id with rollup;

[nishita_1]

Dear AndrisP sir if i use below code the sum of sale and purchase product wise perfect but No 5 product missing for no purchase only sale but i want to show all product. what ever purchase or sale are zero.
SELECT * FROM ( SELECT product_id, SUM(quantity) AS sale FROM order_item group by product_id ) P
JOIN ( SELECT product_id, SUM(quantity) AS purchase FROM pur_item group by product_id ) S
JOIN ( SELECT product_id as Pid FROM product GROUP BY Pid ) I ON I.Pid = P.product_id AND S.product_id = P.product_id

Dear AndrisP sir if is use your below code then result is like below . i don't know sum of sale and purchase is not perfect. please help me sir its very urgent for my sql query.
select
p.product_id
,sum(s.quantity) sale
,sum(c.quantity) purchase
from product p
left join pur_item c on c.product_id = p.product_id
left join order_item s on s.product_id = p.product_id
where c.quantity is not null or s.quantity is not null
group by p.product_id;

please anybody help me i want result for all item sum of product wise data what ever sale or purchase made

[AndrisP]

For first check correct result using separate queries:

select product_id, sum(quantity) from pur_item group by product_id;
select product_id, sum(quantity) from order_item group by product_id;

and i recommend do not use subqueries if it possible - it's too slow

[AndrisP]

Sorry suquery will have to be used here but your join is incorrect - for first select all product ID's from product table then join sale and purcase

select
    p.product_id
    ,s.sale
    ,c.purchase
from (
    select product_id from product
) p
left join (
    select product_id, sum(quantity) sale from order_item group by product_id
) s on s.product_id = p.product_id
left join (
    select product_id, sum(quantity) purchase from pur_item group by product_id
) c on c.product_id = p.product_id
where s.sale is not null or c.purchase is not null;

[nishita_1]

DEAR AndrisP, Thanks for your reply. i am already solve this problem using below code.

SELECT I.product_id, 
       COALESCE(S.sale, 0) AS sale, 
       COALESCE(P.purchase, 0) AS purchase
FROM Product I
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS sale
    FROM order_item 
    GROUP BY product_id
) S ON S.product_id = I.product_id
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS purchase
    FROM pur_item
    GROUP BY product_id
) P ON P.product_id = I.product_id

Dear sir, AndrisP Thanks a lot. you are very helpfully person and your code is perfect. Sir can you tell me, if i want to total sum of sale and purchase date wise. then what code needed. i am already made another table which content date. Thank you

[AndrisP]

get totals:

select
    ifnull(t.product_id,'Total') product_id
    ,sum(t.sale) sale
    ,sum(t.purchase) purchase
from (
    SELECT I.product_id, 
        ifnull(S.sale, 0) AS sale, 
        ifnull(P.purchase, 0) AS purchase
    FROM product I
        LEFT JOIN (
            SELECT product_id, SUM(quantity) AS sale
            FROM order_item 
            GROUP BY product_id
        ) S ON S.product_id = I.product_id
        LEFT JOIN (
            SELECT product_id, SUM(quantity) AS purchase
            FROM pur_item
            GROUP BY product_id
        ) P ON P.product_id = I.product_id
    where S.sale is not null or P.purchase is not null
) t
group by t.product_id with rollup
;

[drmrkrch]

SELECT
p.product_id, SUM(s.quantity) AS 'sale',
SUM(p.quantity) AS 'purchase'
FROM purchase p
LEFT JOIN sale s ON (s.product_id=p.product_id)
GROUP BY p.product_id;

This should give you what you need.