Combination of most ordered categories within an order in mysql

user_19197302 0
Viewed 38

Here is a MYSQL Question:

I have the below order table which has the orderid and within which you will have multiple categories of items

ORDER TABLE

Orderid Category
1 A
1 B
1 C
1 D
2 A
2 C
2 D

Now, here we need to find out the frequency of the combination of categories ordered together. Combination of Category is the combination of two categories in a single order that the user purchased.

The output table should look like this:

Category1 Category2 Count of Orders
A B 1
A C 2
A D 2
B C 1
B D 1
C D 2

This table will give the final count of orders across every combination of categories present in order.

Question: Here i am not able to get all the combination of categories and then ties it up with count of order.Can you please help me here?

mysql sql
3 Answers

It's probably not the best and the most effiency solution, but it works:

SELECT Cat1, Cat2, COUNT(DISTINCT OrderId) FROM (
    SELECT DISTINCT OrderId, LEAST(C1, C2) AS "Cat1", GREATEST(C1, C2) AS "Cat2" FROM (
        SELECT Categories.OrderId, Categories.Category AS "C1", t1.Category AS "C2" FROM Categories
        LEFT JOIN Categories t1 ON Categories.OrderId=t1.OrderId
        WHERE Categories.Category<>t1.Category
    ) t2
) t3
GROUP BY Cat1,Cat2
user_4095330

Try this query:

SELECT category1, category2, COUNT(*)
FROM
(
SELECT t1.orderid, t1.category category1, t2.category category2
FROM Table1 t1
INNER JOIN Table1 t2
ON t1.orderid = t2.orderid AND t1.Category < t2.Category 
) T3
GROUP BY category1, category2;

Check this link for demo: dbfiddle link

user_93856560 
select Category1, category2, count(Orderid) Freq
from(
        select a.orderid ,
        case when a.category > b.category then  b.category else a.category end Category1
        ,case when a.category > b.category then  a.category else b.category end Category2
        from 
        #tempdata a 
        join #tempdata b on a.category != b.category and a.orderid = b.orderid
        group by 
        a.orderid ,
        case when a.category > b.category then  b.category else a.category end,
        case when a.category > b.category then  a.category else b.category end
) b
group by Category1, category2

Inner query is using inner join to get the all combination of category for each ordered but then you will have duplicates for ex. A,B and B,A, both are essentially same so should be counted only once. To remove these scenario, case and group by is being used in inner query which will also remove one extra subquery.

Outer query is just counting the occurrence of same combination of categories.

user_102938300
Related