Concatenate multiple columns and sort within a row

Question

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?

Answer 1

With @Strawberry's suggestion in combination with my "collapse" idea.

SELECT  combo, COUNT(combo) total
FROM
(
    SELECT  id, GROUP_CONCAT(item ORDER BY item) combo
    FROM
    (
        SELECT  id, item1 item
        FROM    cart_table
        UNION ALL
        SELECT  id, item2
        FROM    cart_table
        UNION ALL
        SELECT  id, item3
        FROM    cart_table
    )
)
GROUP BY combo

As much as I dislike nested subqueries...