CODEWITHSUNDEEP
mysql
tagliatelli
tagliatelli

Reputation: 177

MySQL combination values

I have a table called 'transactions' as

transactiondate(date) - customerid(varchar) - item(varchar)
2017-01-01                 1                     coffee
2017-01-01                 2                     tea
2017-01-01                 2                     choc muffin
2017-01-02                 2                     coffee
2017-01-02                 1                     tea
2017-01-02                 1                     choc muffin
2017-01-03                 1                     scone
2017-01-03                 1                     coffee
2017-01-03                 1                     choc muffin
2017-01-03                 2                     tea

I can get most SQL queries working such as finding the most often bought items (using group by and count) but specifically am trying to find the most common combinations of items in a single sale eg. how commonly does someone by a muffin and large tea together etc. Effectively it means grouping rows with the same transactiondate AND two or more by the same customer within that date. Is this where the Having clause comes in, or the dreaded rollup and cube?

Example above as an example shows tea is bought with a choc muffin by the same customer on the same date. I had an idea like having tickboxes in a c# form to select various combinations of product and show how often they are included in the same transaction.

update: using select transactiondate, customerid, count(1) as cnt from transactions group by transactiondate, customerid order by cnt desc does show when the multiple purchases occur but not the actual items.

Upvotes: 2

Views: 658

Answers (1)

SIDU
SIDU

Reputation: 2278

It is very straight and i think the following is plain enough no need wiki?

SELECT transactiondate, customerid, count(distinct item)
FROM transactions
WHERE item in ('coffee', 'choc muffin')
GROUP BY 1,2
HAVING count(distinct item) > 1

Upvotes: 1

Related Questions