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
nishita_1 15
Newbie Poster
Recommended Answers
Jump to PostI recommend use
join
instead ofsubquery
if possible because join works faster. I think you have another table e.g.products
which contains all product id. Selectproduct_id
from products andleft join
sale and purchase. Where clause exclude null values e.g.
Jump to PostSorry 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 Postget 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 …
All 9 Replies
Dani 4,084
The Queen of DaniWeb Administrator Featured Poster Premium Member
nishita_1
commented:
please help me +0
nishita_1 15
Newbie Poster
AndrisP 193
Posting Pro in Training
nishita_1
commented:
dear sir, Thank you you are really helpful. Thanks sir +0
nishita_1 15
Newbie Poster
AndrisP 193
Posting Pro in Training
AndrisP 193
Posting Pro in Training
nishita_1 15
Newbie Poster
AndrisP 193
Posting Pro in Training
drmrkrch 17
Software Engineer
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.