Count combinations of rows in a group with SQL

Question

I have a table with orders and their products:

+-------+---------+
| Order | Product |
+-------+---------+
| A     |       1 |
| A     |       2 |
| A     |       2 |
| A     |       3 |
| B     |       1 |
| B     |       3 |
| B     |       4 |
| C     |       1 |
| C     |       3 |
+-------+---------+

I want to count the occurrences, when two products are ordered together to identity popular product combinations:

+---------------+----------------+-------+
| First product | Second product | Count |
+---------------+----------------+-------+
|             1 |              2 |     1 |
|             1 |              3 |     3 |
|             1 |              4 |     1 |
|             2 |              3 |     1 |
|             2 |              4 |     0 |
|             3 |              4 |     0 |
+---------------+----------------+-------+

Answer 1

Use a self join and group by:

select op1.product, op2.product, count(*)
from orderproduct op1 join
     orderprodut op2
     on op1.order = op2.order and
        op1.product < op2.product
group by op1.product, op2.product
order by count(*) desc;

If you want the most popular combinations, I don't see what combinations with 0 would be needed, so this does not include them.

The above counts all combinations (multiples within an order). If you want to count orders, then use count(distinct):

select op1.product, op2.product, count(distinct op1.order)
from orderproduct op1 join
     orderprodut op2
     on op1.order = op2.order and
        op1.product < op2.product
group by op1.product, op2.product
order by count(*) desc;

Or use select distinct with subqueries. Which is faster depends on the number of duplicate products within orders.