Calculation and multiple joins in mysql

Question

1.products (Products Table)

1.productid 

  001         
  002         
  003         

2.productname

  x
  y
  z

2.invoiceqty (InvoiceQuantity Table)

1.productid

  001       
  001      
  002       
  002 

2.invqty

  10
  20
  10
  05

3.grnqty (GRN Quantity Table)

1.productid 

  001        
  002        
  003        

2.grqty

  50
  50
  50

4.returninqty (Return In Quantity Table)

1.productid 

  002
  002

2.retinqty

  02
  03

5.returnoutqty (Return Out Quantity Table)

1.productid 

  001
  001

2.retoutqty

  01
  01

I need to perform this query to view stock balance stockbalance calculation like this

(grnqty+returninqty)-(invoiceqty+returnoutqty) = StockBalance

Please Tell me how to write a query for calculating stock balance like this i have develop a inventory control system in java/MySQL please help me on this query

6.Stocks(QUERY)

1.productid 

  001
  002
  003

2.Stockqty

  40
  33
  50

Answer 1

Try to compute the quantities for each table using a subquery. It doesn't work with LEFT JOINs because we would have a cartesian product across the tables.

SELECT
    p.productid,
(
    SELECT COALESCE(SUM(grnqty), 0) 
    FROM grnqty g 
    WHERE g.productid = p.productid
) 
 +
(
    SELECT COALESCE(SUM(returninqty), 0) 
    FROM returninqty ri 
    WHERE ri.productid = p.productid
)     
 - 
(
    SELECT COALESCE(SUM(invoiceqty), 0) 
    FROM invoiceqty i 
    WHERE i.productid = p.productid
)
 -
(
    SELECT COALESCE(SUM(returnoutqty), 0) 
    FROM returnoutqty ro 
    WHERE ro.productid = p.productid
) as stock

FROM products p

Also, you could create a function to compute the stock. You could easily reuse it for other queries.

DELIMITER $$

CREATE FUNCTION `get_product_stock` (IN p_id_product INT) RETURNS INT
BEGIN

DECLARE stock INT;

SET stock =
    (
        SELECT COALESCE(SUM(grnqty), 0)
        FROM grnqty
        WHERE productid = p_id_product
    )
     +
    (
        SELECT COALESCE(SUM(returninqty), 0)
        FROM returninqty
        WHERE productid = p_id_product
    )
     -
    (
        SELECT COALESCE(SUM(invoiceqty), 0)
        FROM invoiceqty
        WHERE productid = p_id_product
    )
     -
    (
        SELECT COALESCE(SUM(returnoutqty), 0)
        FROM returnoutqty
        WHERE productid = p_id_product
    );

RETURN stock;

END $$

DELIMITER ;

You can use the function like this:

// get the stock for product with id 1
SELECT get_product_stock(1); 

// get all the products with their stock
SELECT productid, name, get_product_stock(productid) as stock FROM products; 

Answer 2

This query is according to your formula but the result you displayed is not produced because i think your formula may have any problem

SELECT
  p.productid,

  (ifnull(g.g_sum,0) + ifnull(rq.rq_sum,0))-(ifnull(i.i_sum,0) + ifnull(ro.ro_sum,0)) as StockBalance,

  ifnull(g.g_sum,0) as GRN_QUANTITY,
 ifnull( rq.rq_sum,0) as RETURN_IN_QUANTITY,
 ifnull( i.i_sum,0) as INVENTORY_QUANTITY,
 ifnull( ro.ro_sum,0) as RETURN_OUT_QUANTITY
from products as p
  left join (select
           productid,
           sum(grnqty)  as g_sum
         from grnqty group by productid) as g
    on g.productid = p.productid
  left join (select
           productid,
           sum(retinqty) as rq_sum
         from returninqty group by productid) as rq
    on rq.productid = p.productid
  left join (select
           productid,
           sum(invqty)  as i_sum
         from invoiceqty group by productid) as i
    on i.productid = p.productid
  left join (select
           productid,
           sum(retoutqty) as ro_sum
         from returnoutqty group by productid) as ro
    on ro.productid = p.productid