How to get total stock left using sql-CodePudding

I try to get remaining stock using sql.whats wrong with me.

SELECT (SELECT SUM(quantity) as stock 
        FROM stock WHERE product_id='4' 
        GROUP BY product_id 
        - 
        SELECT SUM(qty) as sales 
        FROM order_details 
        WHERE product_id='4'
       )

CodePudding user response：

I think that this is what you need.
Please provide table definitions and sample data and required output if it is not right.

SELECT
  product_id,
  SUM(st.quantity) as stock,
  SUM(od.qty) as sales
FROM
  stock st
LEFT JOIN order_details od
ON st.product_id = od.product_id
WHERE st.product_id = '4'
GROUP BY product_id;

We could also use the query following, which is closer to your original query.

SELECT 
  'stock' as "item",
  SUM(quantity) as "value" 
FROM stock WHERE product_id='4' 
GROUP BY product_id 
  UNION ALL
SELECT 
  'sales',
  SUM(qty) 
FROM order_details 
WHERE product_id='4';

CodePudding user response：

This is my nuclear weapon for dealing with getting the balance and stuff like that and you will never ever get any missing info for this, here is How I made it, I am doing this by creating 3 different Views, one for Order, one for Sold, and the combination of both (the final summary)

UPDATE: in the real example you have the ItemID in a table and that is the Primary key (not null no duplicate), you use that as a reference for all itemID get both sold and remaining. for me, I have created items with idItem (PK, AI) and itemName

First, Create a view for stock v_stock :

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `v_stock` AS
    SELECT 
        `items`.`iditem` AS `ItemID`,
        SUM(IFNULL(`stock`.`quantity`, 0)) AS `StockQTY`
    FROM
        (`items`
        LEFT JOIN `stock` ON (`items`.`iditem` = `stock`.`product_id`))
    GROUP BY `items`.`iditem`

Second, Create view for order v_order:

    CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `v_order` AS
    SELECT 
        `items`.`iditem` AS `ItemID`,
        SUM(IFNULL(`order_details`.`qty`, 0)) AS `SoldQTY`
    FROM
        (`items`
        LEFT JOIN `order_details` ON (`items`.`iditem` = `order_details`.`item_id`))
    GROUP BY `items`.`iditem`

Finally the summary view v_itembalance:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `v_itembalance` AS
    SELECT 
        `v_stock`.`ItemID` AS `ItemID`,
        SUM(`v_stock`.`StockQTY`) AS `inStock_QTY`,
        SUM(`v_order`.`SoldQTY`) AS `Sold_QTY`,
        SUM(`v_stock`.`StockQTY`) - SUM(`v_order`.`SoldQTY`) AS `Remaining QTY`
    FROM
        (`v_stock`
        JOIN `v_order` ON (`v_stock`.`ItemID` = `v_order`.`ItemID`))
    GROUP BY `v_stock`.`ItemID` , `v_order`.`ItemID`

Now, the idea here why I am using INNER not left or right is because or void or null in any of side (order or stock),

how I can get information? well easy:

select * from v_itembalance

Do I need the info for item 4?

select * from v_itembalance where ItemID = '4'

let me know if you still need any help or you don't understand any of this, feel free to hit the upvote :)

select * from v_itembalance;
select * from stock;
select * from order_details;
select * from v_itembalance where ItemID = '4';

Feel free if you have any other needs :P