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
– David, Commented Apr 13, 2016 at 12:31
It's an old db that I working now, would take me forever to change the structure now. — Santosh Achari
– Santosh Achari, Commented Apr 13, 2016 at 12:33
@lad2025 - but it wouldn't add the Paperoni and Corn quantities sold. — Santosh Achari
– Santosh Achari, Commented Apr 13, 2016 at 12:33
@SantoshAchari: This may help: stackoverflow.com/questions/17942508/… 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
– David, Commented Apr 13, 2016 at 12:36

Lukasz Szozda · Accepted Answer · 2016-04-13 12:47:42Z

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

Collectives™ on Stack Overflow

Check counts on column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related