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

22222.jpg

Recommended Answers

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.


       
Jump to Post

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 …
Jump to Post

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 …
Jump to Post

All 9 Replies

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

commented: please help me +0

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

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;
commented: dear sir, Thank you you are really helpful. Thanks sir +0

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

real_result.PNG

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;

your_result.PNG

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

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

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;

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

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
;

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.