MySQL concatenate multiple columns and sort within a row

Vote count:

0

I have a table that has the following fields:

+------------------------------+
|  id  | cart1 | cart2 | cart3 |
|------------------------------|
|  1   |  ball |  soap |  NULL |
|  2   | apple | towel | paper |
|  3   |  soap |  ball |  NULL |
| .... | ..... | ..... | ..... |
+------------------------------+

I want the following output:

+-----------------------------------------+
| item1 | item2 | item3 | num_appearances |
|-----------------------------------------|
| ball  | soap  |  NULL |        2        |
| apple | towel | paper |        1        |
| ..... | ..... | ..... | ............... |
+-----------------------------------------+

Basically, cart1, cart2, and cart3 define a person's cart, but the order doesn't matter, and I want to count up the number of times a set of items were bought together, again with order not mattering. So apple, towel, paper appeared once in the sample table, and ball and soap appeared twice.

What I think I need to do is sort item1, item2, and item3, concatenate them, and group by that concatenated value. So group_concat sounds great, and I can group by id, or some other column that is distinct for each row. But so far I have group_concat(item1, item2, item3 [ORDER BY WHAT]). But how do I sort a list of columns and return that concatenated, sorted list?

asked 20 secs ago

Mike C

167