How to correctly separate this query

Question

I'm trying to sort through my table to find the frequent categories in my orders. After conducting this query

SELECT 
  ccd.cart_id, 
  mp.category_name, 
  ccd.quantity 
FROM 
  customer_orders co
  JOIN customer_cart_dtls ccd
    ON co.order_cart = ccd.cart_id
  JOIN merchant_products mp
    ON ccd.product_id = mp.product_id

which yields this result

So from that query Cart #2006........63 has 9 items. 1 from eatables, 3 From fruits, 2 From cleaning, and 3 from Snacks. All of them quantity 1 except for the second entry of cleaning which has two. How can I alter my query so that I get 10 items all with quantity 1?

Which would look like this

so you only want items whose quantity is 1? And you also want max 10 items, is that correct? How about `WHERE co.created_on BETWEEN '2020-07-01' AND '2020-07-31' AND quantity = 1 LIMIT 10` . If that's not what you mean then please explain more clearly — ADyson, Aug 10 '20 at 23:43
what I want to do is to have the items with quantity 1+ be added as a row to that cart. So instead of cart_id having 9 items, it would have 10 since the cleaning row that had qty 2 is now separated into two rows of cleaning. In regards to the where clause, I edited out of the question, as it does not matter for the question at hand :) — Neykof, Aug 10 '20 at 23:48
Ok. Showing some sample source data, and a table showing the expected result, would help everyone a lot if you can, please. — ADyson, Aug 10 '20 at 23:54
[this is what I want the data to look like](https://imgur.com/gallery/fu08w8t) — Neykof, Aug 11 '20 at 00:04
Please use the "edit" button under your question to edit the main post with extra info. Comments aren't the place to bury vital info which is relevant to your question. Also, sample source data as well would be helpful, so we can see more easily what transformation needs to be made. Thanks. — ADyson, Aug 11 '20 at 00:05
Sorry about that, I added what I want the result to look like in the original post. By source data, do you mean screenshots of the tables being queried? — Neykof, Aug 11 '20 at 00:11
Well putting the actual data as text in a table in your question would be a lot better than screenshots. That way, people can copy it and re-use it to test queries. If you want to be _really_ helpful, you could create a pre-defined SQLFiddle or DBFiddle containing the tables and the sample data, ready to query — ADyson, Aug 11 '20 at 00:12
is there anyway we could chat via discord or any other medium so I could send you the exported csv files? I dont know how to create a pre-defined SQLFiddle or DBFiddle containing the tables and the sample data — Neykof, Aug 11 '20 at 00:36
You just go to http://sqlfiddle.com/ and start typing/pasting. (You can speed up the job of course by using your database admin tool (e.g. Workbench, phpMyAdmin or whatever) to generate some create and insert statements from the current database, which you can then paste into the fiddle) — ADyson, Aug 11 '20 at 00:38
what I'm trying to do is what @GordonLindoff did below, but I can't use CTE's unfortunately. — Neykof, Aug 11 '20 at 02:23

score 1 · Answer 1 · answered Aug 10 '20 at 23:57

Are you trying to count how many items come from each category by splitting every item into an individual row and then using COUNT? If so, I don't think you necessarily need to go down that route. It will likely be a lot easier to simply use the SUM aggregate function after grouping by category_name. It might look something like this:

SELECT mp.category_name, SUM(ccd.quantity)
FROM customer_orders AS co
JOIN customer_card_dtls AS ccd ON co.order_cart = ccd.cart_id
JOIN merchant_products AS mp ON ccd.product_id = mp.product_id
GROUP BY mp.category_name

If you want to also see cart IDs then just add the appropriate columns to your SELECT and GROUP BY statements

Thank you! I don't necessairly want to see how many items come from each category, I want to split them because I want to see the frequent combinations that are being created in all the orders. [such as this example](https://imgur.com/gallery/XnL8Bqi) — Neykof, Aug 11 '20 at 00:08

Gordon Linoff · Accepted Answer · 2020-08-11T14:39:47.877

1

You want to split the individual rows into multiple rows. One method uses recursive CTEs:

WITH RECURSIVE t as (
      SELECT ccd.cart_id, mp.category_name, ccd.quantity 
      FROM customer_orders co JOIN
           customer_cart_dtls ccd
           ON co.order_cart = ccd.cart_id JOIN
           merchant_products mp
           ON ccd.product_id = mp.product_id
     ),
     cte as (
      SELECT cart_id, category_name, quantity, 1 as n
      FROM t
      UNION ALL
      SELECT cart_id, category_name, quantity, n + 1
      FROM cte
      WHERE n < quantity
    )
SELECT cart_id, category_name, 1 as quantity
FROM cte;

Here is a db<>fiddle.

EDIT:

You can join in a list of quantities -- easier if you have a tally table of some sort:

SELECT ccd.cart_id, mp.category_name, 1 as quantity 
FROM customer_orders co JOIN
     customer_cart_dtls ccd
     ON co.order_cart = ccd.cart_id JOIN
     merchant_products mp
     ON ccd.product_id = mp.product_id JOIN
     (SELECT 1 as n UNION ALL
      SELECT 2 as n UNION ALL
      SELECT 3 as n UNION ALL
      SELECT 4 as n UNION ALL
      SELECT 5 as n 
     ) n
     ON n.n <= ccd.quantity;

You can also construct the table using variables from an existing table (if it is big enough):

(select (@rn := @rn + 1) as n
 from customer_orders cross join
      (select @rn := 0) params
 limit 100  -- say that 100 is big enough
) n

edited Aug 11 '20 at 14:39

answered Aug 11 '20 at 01:58

Gordon Linoff

1,242,037
58
646
786

Thank you! I tried running it but I'm using mariadb and I get syntax error. Is there any other way of doing this? Since I can't change any settings as this is a work db – Neykof Aug 11 '20 at 02:17
@Neykof . . . It should run fine in MariaDB. I added a db<>fiddle. – Gordon Linoff Aug 11 '20 at 02:34
I get this when I try to run it, ```Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RECURSIVE t as ( SELECT ccd.cart_id, mp.category_name, ccd.quantity ' at line 1 0.062 sec``` – Neykof Aug 11 '20 at 03:02
I'm running mySQL 5.7, [this](https://imgur.com/a/fsXaGRK) is the result I get when i run it on 5.7 in db<>fiddle – Neykof Aug 11 '20 at 05:13
@Neykof . . . This requires MySQL 8+, as shown in the db<>fiddle. – Gordon Linoff Aug 11 '20 at 10:58
Is there an alternate way you can suggest I do the query? – Neykof Aug 11 '20 at 14:31
Thank you so much @GordonLinoff! I really appreciate your patience – Neykof Aug 11 '20 at 16:47
Hello @Gordon, if you have the chance can you look at the question I just posted? hopefully you can help me there too – Neykof Aug 11 '20 at 22:37
[this](https://stackoverflow.com/questions/63367284/finding-all-order-combination?noredirect=1#comment112050496_63367284) is the link to the question – Neykof Aug 11 '20 at 23:16

How to correctly separate this query

2 Answers2