Click here to Skip to main content
15,746,652 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
I have been working on Client project. I need to get stock from Purchased product QTY and Sold QTY.

Here my SQL
SELECT DISTINCTROW tbl_itemmaster.product_code,tbl_itemmaster.unittype,tbl_itemmaster.color,SUM(tbl_purchasedetails.qty),SUM(tbl_saledetails.qty),(SUM(tbl_purchasedetails.qty)-SUM(tbl_saledetails.qty)) 
FROM (tbl_itemmaster LEFT JOIN tbl_saledetails ON tbl_itemmaster.[product_code] = tbl_saledetails.[product_code]) 
LEFT JOIN tbl_purchasedetails ON tbl_itemmaster.[product_code]=tbl_purchasedetails.[product_code] GROUP BY tbl_itemmaster.product_code,tbl_itemmaster.unittype,tbl_itemmaster.color

Below is the result of sql
Product Code   Unit Type  Color     Purchase QTY  Sold QTY  Stock
1001           KiloGram   Red       500           
500            Kilogram   White     1033          300       733
570            Kilogram   Black   
600            Kilogram   Pink   
9005           Kilogram   Magenta   800.5         
900            Kilogram   Green   

Product code 500 has three times purchase entry i.e. 500+400+133 = 1033 Purchased Qty
Product code 500 has only ONE time sale entry i.e. 100 = 100 Sold Qty
Product code 500 suppose to has 933 Stock Qty
BUT why I getting 300 in sold qty ?
Where my query is lacking?
[no name] 2-Jan-16 11:55am    
I think to help, we Need to know the Content of the tables.

1 solution

I think its because you are using the DISTINCTROW any 2 rows with the same values will only return 1
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900