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

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:

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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.