I'm trying to create a pivot table like output (cross table? Not entirely sure about the difference). The idea is to have rows, columns and summary values (specifically: distinct count). The data looks like this:
Transactions table:
product_id seller_id seller_country buyer_id
buyers table
buyer_id buyer_country
There could be multiple rows for the same product, and the same buyer can buy from multiple sellers multiple times.
Buyer table has unique rows for each buyer, so while the same buyer can appear on multiple rows in the transactions table, he'll appear just on one row in the buyers table.
What I want to create is a matrix where the rows are for sellers' countries, columns for buyers' countries, and the values in the table count the number of unique buyers from each country who bought from each country. Count the distinct buyers each cell.
I tried several things but ultimately couldn't get past a row group by. Searched online for solutions but nothing was relevant for this very problem. This is what I managed to do with MS access:
TRANSFORM Count(Dummy_users.buyer_id) AS CountOfbuyer_id
SELECT Dummy_transactions.seller_country
FROM Dummy_users LEFT OUTER JOIN Dummy_transactions ON Dummy_users.buyer_id = Dummy_transactions.buyer_id
GROUP BY Dummy_transactions.seller_country
PIVOT Dummy_users.buyer_country;
but I don't see PIVOT is acceptable anywhere in mysql (no search results which offer that solution), and also for some reason it broke when I tried to add the distinct element to the count.
Can you help me with the syntax please?
Here is some dummy data if needed: transactions: https://docs.google.com/spreadsheets/d/1vHzjmSkaQ5G5lTdV_M6h7gp8zR4Kfqz70gL4TeID2jQ/ buyers: https://docs.google.com/spreadsheets/d/18HXZ_GotqEMDhxytYKxNeD4W6-PfqUvkcWh3DBuCYPA/
Many thanks!