Q:

Check counts on column

I am working with three tables, which store sales data along with items and add ons used.
Sales
 sale_id   | sale_amount | sale_time 
---------------------------------------
   1       | 20         | 2016-04-11 11:43:00 
   2       | 30         | 2016-04-11 11:53:00 

Sale_Items, where add ons used are stored comma separated
sale_item_id | item_id | qty | price | addon_ids 
-------------------------------------------------
1            | 1       | 2   | 10    | 3,4 
2            | 1       | 3   | 10    | 2,4 

Items 
item_id | item_name 
----------------------
  1     |   Pizza
  2     |   Paperoni
  3     |   Corn
  4     |   Salami

I want to run a query to get a specific kind of result that also lets me know the counts of add ons used
item_id | Item Name | qty_sold
------------------------------
1       | Pizza     | 5
2       | Paperoni  | 3
3       | Corn      | 2
4       | Salami    | 5

How to do this via MySQL query?

A:

Correct way:
Normalize your schema. addon_ids should not store non-atomic values, especially when you want use them to join.

Workaround:
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 FIND_IN_SET(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;

LiveDemo
Output:
╔═════════╦═══════════╦═════╗
║ item_id ║ item_name ║ qty ║
╠═════════╬═══════════╬═════╣
║       1 ║ Pizza     ║   5 ║
║       2 ║ Paperoni  ║   3 ║
║       3 ║ Corn      ║   2 ║
║       4 ║ Salami    ║   5 ║
╚═════════╩═══════════╩═════╝

