MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE sample_table (
id int,
item_id varchar(2),
type varchar(3),
quantity int
)
INSERT INTO sample_table
VALUES
(1, 'X1', 'IN', 10),
(2, 'X1', 'OUT', 3),
(3, 'X2', 'IN', 12),
(4, 'X2', 'OUT', 2)
SELECT * FROM sample_table


id
item_id
type
quantity


1
X1
IN
10

2
X1
OUT
3

3
X2
IN
12

4
X2
OUT
2


SELECT a.ITEM_ID, 
       a.`Item In`, 
       a.`Item Out`, 
      (a.`Item In` - a.`Item Out`) AS `Final Qty`
FROM
(SELECT 
    ITEM_ID, 
    SUM( IF( TYPE = 'IN', QUANTITY, 0 ) ) AS `Item In`,  
    SUM( IF( TYPE = 'OUT', QUANTITY, 0 ) ) AS `Item Out`
  FROM 
    sample_table 
GROUP BY 
    ITEM_ID) a


ITEM_ID
Item In
Item Out
Final Qty


X1
10
3
7

X2
12
2
10