CODEWITHSUNDEEP
mysqlsql
techJava
techJava

Reputation: 151

Inventory stock calculation query - Mysql

I have two separate sql queries which calculate total purchase quantity and total sold quantity.

    SELECT b.book_id, b.title, SUM(pid.quantity) AS 'Purchase Quantity'
    FROM purchase_invoice_det pid JOIN book b ON pid.book_id = b.book_id
    JOIN purchase_invoice pi ON pid.purchase_inv_id = pi.purchase_inv_id 
    WHERE pi.purchase_inv_date BETWEEN '2020-01-01' AND '2020-12-31' AND branch_id = 2 GROUP BY title;


    SELECT b.book_id, b.title, SUM(sid.quantity) AS 'Sold Quantity'
    FROM sales_invoice_det sid JOIN book b ON sid.book_id = b.book_id
    JOIN sales_invoice si ON sid.sales_inv_id = si.sales_inv_id
    WHERE si.sales_inv_date BETWEEN '2020-01-01' AND '2020-12-31' AND branch_id = 2 GROUP BY title;

Now to calculate the available stock of each product I need to subtract total sold quantity from purchase quantity by combining two queries.

I tried to join as follows but it's calculating available stock as 2x - double of the original available quantity.

    SELECT b.book_id, b.title, SUM(pid.quantity)-SUM(sid.quantity) AS 'Available Stock',
    FROM purchase_invoice_det pid JOIN book b ON pid.book_id = b.book_id
    JOIN purchase_invoice pi ON pid.purchase_inv_id = pi.purchase_inv_id
    JOIN sales_invoice_det sid ON sid.book_id = b.book_id
    JOIN sales_invoice si ON sid.sales_inv_id = si.sales_inv_id 
    WHERE pi.purchase_inv_date BETWEEN '2020-02-01' AND '2020-12-31'
    AND si.sales_inv_date BETWEEN '2020-01-01' AND '2020-12-31' 
    AND pi.branch_id = 2 AND si.branch_id = 2 GROUP BY b.title;

Any ideas how to combine above two sql queries to calculate available stock of each product ?

Upvotes: 1

Views: 846

Answers (1)

Syed Mushtaq
Syed Mushtaq

Reputation: 321

Try this approach .

SELECT A.BOOK_ID,B.TITLE,A.Purchase_Quantity-B.Sold_Quantity from (SELECT b.book_id, b.title, SUM(pid.quantity) AS Purchase_Quantity
    FROM purchase_invoice_det pid JOIN book b ON pid.book_id = b.book_id
    JOIN purchase_invoice pi ON pid.purchase_inv_id = pi.purchase_inv_id 
    WHERE pi.purchase_inv_date BETWEEN '2020-01-01' AND '2020-12-31' AND branch_id = 2 GROUP BY title) A , 
    (SELECT b.book_id, b.title, SUM(sid.quantity) AS Sold_Quantity
    FROM sales_invoice_det sid JOIN book b ON sid.book_id = b.book_id
    JOIN sales_invoice si ON sid.sales_inv_id = si.sales_inv_id
    WHERE si.sales_inv_date BETWEEN '2020-01-01' AND '2020-12-31' AND branch_id = 2 GROUP BY title)B
WHERE  A.BOOK_ID=B.BOOK_ID AND A.TITLE =B.TITLE ;

Upvotes: 1

Related Questions