Demo, Sql Server - rextester

Language: Layout:

CREATE TABLE Items (
   item_id   INTEGER  NOT NULL PRIMARY KEY 
  ,item_name VARCHAR(11) NOT NULL
);
INSERT INTO Items (item_id,item_name) VALUES (1,'Pizza');
INSERT INTO Items (item_id,item_name) VALUES (2,'Paperoni');
INSERT INTO Items (item_id,item_name) VALUES (3,'Corn');
INSERT INTO Items (item_id,item_name) VALUES (4,'Salami');

CREATE TABLE Sale_Items(
   sale_item_id INTEGER  NOT NULL PRIMARY KEY 
  ,item_id      BIT  NOT NULL
  ,qty          INTEGER  NOT NULL
  ,price        INTEGER  NOT NULL
  ,addon_ids    VARCHAR(5) NOT NULL
);
INSERT INTO Sale_Items(sale_item_id,item_id,qty,price,addon_ids) VALUES (1,1,2,10,'3,4');
INSERT INTO Sale_Items(sale_item_id,item_id,qty,price,addon_ids) VALUES (2,1,3,10,'2,4');

SELECT item_id, item_name, SUM(qty) AS qty
FROM (
SELECT i.item_id, i.item_name, SUM(qty) AS qty
FROM Items i
LEFT JOIN Sale_items si
  ON i.item_id = si.item_id
GROUP BY i.item_id, i.item_name
UNION ALL
SELECT i.item_id, i.item_name, SUM(qty)
FROM Items i
LEFT JOIN Sale_items si
  ON CHARINDEX(CAST(i.item_id AS VARCHAR(100)), si.addon_ids) > 0
GROUP BY i.item_id, i.item_name
) AS sub
GROUP BY item_id, item_name

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 6, absolute service time: 0,19 sec