How do I make a possible combination using MySQL?

Question

I'd like to make all possible combinations of "FRUIT" and calculate the SUM of "price."

    INSERT INTO MARKET(FRUIT, PRICE) VALUES ("APPLE", 2) ,("BANANA", 3) ,("GRAPE", 1)

In this case, the combinations that can be made in the "MARKET" are "APPLE", "BANANA", "GRAPE", "APPLE+BANANA", "APPLE+GRAPE", "BANANA+GRAPE", and "APPLE+BANANA+GRAPE".

So I want to select a table like the one below.

    "APPLE" 2
    "BANANA" 3
    "GRAPE" 1
    "APPLE+BANANA" 5
    "APPLE+GRAPE"3
    "BANANA+GRAPE" 4
    "APPLE+BANANA+GRAPE" 6

Is it possible to make the given table to the table above?

If you're using MySQL 8.x you can use a recursive CTE for this. I don't think it's easy if you're using older MySQL. — Barmar, Jul 27 '23 at 04:54
[mysql find all the possible combinations of one column](https://stackoverflow.com/questions/22509413/mysql-find-all-the-possible-combinations-of-one-column) — Abra, Jul 27 '23 at 04:55
[How to get all possible combinations among rows of the same table](https://stackoverflow.com/questions/40212606/how-to-get-all-possible-combinations-among-rows-of-the-same-table) — Abra, Jul 27 '23 at 04:56
[How to use mysql JOIN without ON condition?](https://stackoverflow.com/questions/16470942/how-to-use-mysql-join-without-on-condition) — Abra, Jul 27 '23 at 04:58
Did you try [searching](https://stackoverflow.com/search) before posting your question? The links in my other comments came from Googling for [mysql select all possible combinations](https://www.google.com/search?q=mysql+select+all+possible+combinations) — Abra, Jul 27 '23 at 05:02
@Abra Those are all just pairs. The question wants all possible lengths. — Barmar, Jul 27 '23 at 05:14

score 0 · Answer 1 · answered Jul 27 '23 at 11:48

You can use a recursive CTE to find all combinations. For example:

with recursive
n as (
  select cast(fruit as char(100)) as fruit, price from market
 union all
  select concat(m.fruit, '+', n.fruit), m.price + n.price
  from n
  join market m on m.fruit > n.fruit
)
select * from n;

Result:

 fruit               price 
 ------------------- ----- 
 apple               2     
 banana              3     
 grape               1     
 banana+apple        5     
 grape+apple         3     
 grape+banana        4     
 grape+banana+apple  6

See running example at db<>fiddle.

How do I make a possible combination using MySQL?

1 Answers1