0

Let's say I have two tables, T1 and T2, with exact same columns: ClientId and ItemId.

I want to calculate: for each ClientId in T2, how many ItemIds exist in T2 but not in T1.

Example:

T1

ClientId ItemId
1 11
2 21
2 22
2 23

T2

ClientId ItemId
2 22
2 23
2 24
2 25
3 31

Expected Output

ClientId ItemIdCount
2 2
3 1

Explanation:

In T2, for ClientId=2, we have four ItemIds: 22, 23, 24, 25. However, this same client ID in T1 has two of those values: 22, 23. Thus, the ItemIdCount for this client ID is two.

havij
  • 1,030
  • 14
  • 29
  • See https://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 for how to get all the rows in T2 that aren't in T1. Then use `COUNT(*) GROUP BY ClientID` to get the row counts. – Barmar Apr 20 '23 at 23:59
  • @Barmar but that would give me only the count of `ItemID`s for each `ClientId` that exists in T2 but not in T1. But I also would like to exclude all the `ItemId`s in T1 for that `ClientId` and then do the count. – havij Apr 21 '23 at 00:31
  • "only the count of ItemIDs for each ClientId that exists in T2 but not in T1" That's exactly what you said in the question. – Barmar Apr 21 '23 at 00:37
  • Please update the question with sample data and the desired result. – Barmar Apr 21 '23 at 00:37
  • Thanks for your answer here, but what you quoted above is not what I have in my question. It exactly says "for each ClientId in T2, how many ItemIds exist in T2 but not in T1". @Barmar – havij Apr 21 '23 at 00:47
  • I still don't understand the difference. That's why I asked you to post an example in the question. – Barmar Apr 21 '23 at 14:55
  • @Barmar added some example values, hope this clarifies what I am trying to ask. – havij Apr 21 '23 at 17:52
  • 1
    https://www.db-fiddle.com/f/uN8rA33mzinf5Q6JRcMg6d/0 – Barmar Apr 21 '23 at 19:04

0 Answers0