Cfrgtkky

I have those 3 tables

Warehouse



Id  name        

1   Warehouse 1     

2   Warehouse 2     





Items



Id  description     

1   Item 1      

2   Item 2      

3   Item 3







itemmovement



itemid     qtyin    qtyout  warehouseid

1            2                   1

1                      1         1

1            2                   2

2            1                   1

2            3                   1

2                      1         1

2            1                   2





Result      

ItemId  SumQuantityWarehouse1   SumQuantityWarehouse2

1              1                         2

2              3                         1

3              0                         0

I need the result to sum up sum(qtyin)-sum(qtyout) with respect to itemid and warehouseid , by listing all the warehouses in columns with the quantities
of each item as shown in the result

The only practical way to achieve this in MySQL is by using a stored procedure to build a dynamic query based on the list of warehouses. The query has to use conditional aggregation to build the result table. For your sample data, the query that is built looks like this:

SELECT i.Id

     , SUM(CASE WHEN m.warehouseID=1 THEN COALESCE(m.qtyin, 0) - COALESCE(m.qtyout, 0) ELSE 0 END) AS `SUMWarehouse 1`

     , SUM(CASE WHEN m.warehouseID=2 THEN COALESCE(m.qtyin, 0) - COALESCE(m.qtyout, 0) ELSE 0 END) AS `SUMWarehouse 2`

FROM items i 

LEFT JOIN itemmovement m ON m.itemid = i.Id

GROUP BY Id

Here is the procedure:

DELIMITER //

DROP PROCEDURE IF EXISTS stocktake //

CREATE PROCEDURE stocktake()

BEGIN

  DECLARE wid INT;

  DECLARE wname VARCHAR(20);

  DECLARE query TEXT DEFAULT '';

  DECLARE finished INT DEFAULT 0;

  DECLARE whouse_cursor CURSOR FOR SELECT Id, name FROM warehouse;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

  OPEN whouse_cursor;

  SET @query = 'SELECT i.Id';

get_whouse: LOOP

  FETCH whouse_cursor INTO wid, wname;

  IF finished = 1 THEN 

    LEAVE get_whouse;

  END IF;

  SET @query = CONCAT(@query, ', SUM(CASE WHEN m.warehouseID=', wid, ' THEN COALESCE(m.qtyin, 0) - COALESCE(m.qtyout, 0) ELSE 0 END) AS `', wname, '`');

  END LOOP get_whouse;

  SET @query = CONCAT(@query, ' FROM items i LEFT JOIN itemmovement m ON m.itemid = i.Id');

  SET @query = CONCAT(@query, ' GROUP BY Id');

  PREPARE stmt FROM @query;

  EXECUTE stmt;

  DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

And the output for your sample data:

Id  Warehouse 1     Warehouse 2

1   1               2

2   3               1

3   0               0

Demo on dbfiddle

Alternate demo showing the procedure working with 5 warehouses.