Check counts on column

Question

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?

Start by fixing the data. That `addon_ids` column is breaking relational design. Those should be separate records. Once they're separate records, counting them would be done with the `COUNT()` function. — David, Apr 13 '16 at 12:31
It's an old db that I working now, would take me forever to change the structure now. — Santosh Achari, Apr 13 '16 at 12:33
@lad2025 - but it wouldn't add the Paperoni and Corn quantities sold. — Santosh Achari, Apr 13 '16 at 12:33
@SantoshAchari: This may help: http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows It's going to be an ugly operation any way you look at it, because you're doing string parsing instead of querying data. Databases are great at querying data, they're not really designed for string parsing and other value manipulation logic. (An application programming language would likely make that part easier.) You either deal with the cost of fixing the data, or you deal with the cost of leaving it broken. Neither are cheap, but one of the two actually solves a problem. — David, Apr 13 '16 at 12:36

score 1 · Accepted Answer · answered Apr 13 '16 at 12:47

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 ║
╚═════════╩═══════════╩═════╝

Check counts on column

1 Answers1