CODEWITHSUNDEEP
mysql
Rida Sukmara
Rida Sukmara

Reputation: 13

Finding the number of items which are associated to a specified set of related items

I have a table, Transactions, containing this data:

+------+---------+
|  id  |  item   |
+------+---------+
| 1    |    A    |
| 1    |    B    |
| 2    |    A    |
| 2    |    D    |
| 3    |    C    |
| 3    |    D    |
| 4    |    E    |
| 4    |    C    |
| 5    |    A    |
| 5    |    B    |
| 5    |    C    |
+------+---------+

How would I find the number of transactions that contain a specified set of items?

For example, the number of transactions that contain items A and B is two, because only transactions 1 and 5 contain both of those items.

Upvotes: 0

Views: 30

Answers (3)

Rick James
Rick James

Reputation: 142298

A bit shorter, and probably a bit faster:

SELECT  id
    FROM  Transactions
    WHERE  item IN ('A', 'B')
    GROUP BY  id
    HAVING  COUNT(DISTINCT item) = 2

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Im not sure what will be your input, but to solve your question this will do

Sql Fiddle Demo

This will count transactions with both items A and B

SELECT count(*)
FROM (             
      SELECT id, count(*)
      FROM Table1
      GROUP BY `id`
      HAVING COUNT( CASE WHEN  `item` = 'A' THEN 1 END) > 0
         AND COUNT( CASE WHEN  `item` = 'B' THEN 1 END) > 0
      ) T

OUTPUT

| count(*) |
|----------|
|        2 |

NOTE

The count(*) in the subquery is optional, was just add it for debug to have a visual feedaback of that subquery.

Upvotes: 2

Marc B
Marc B

Reputation: 360702

Probably something like this, though not tested:

SELECT A.id, count(A.item) AS cntA, count(B.item) AS cntB
FROM table AS A
LEFT JOIN table AS B
   ON (A.item = B.item) AND (A.id <> B.id)
GROUP BY A.id
HAVING cntA = cntB

Self join the table on different IDs having the same items, and compare the count of items. If the count is the same, then the two IDs have the same items.

Upvotes: 0

Related Questions