SQLite 3.24 – Counting occurrences of value grouped by id without using window functions

Apologies if this has been asked, but maybe I’m not familiar with the language to ask for what I want, and I looked through dozens of other questions but don’t seem to be finding what works.

I’m working on a tool for a trading card game, a deck is a list of cards, I have a table mapping deckId to cardId

Given the example (rowid not shown)

deckId| cardId
-----------
1  | 321
1  | 50
1  | 100
1  | 125
2  | 321
2  | 50
2  | 99
2  | 87
3  | 50
3  | 12
3  | 5
3  | 47
4  | 999
4  | 998
4  | 997
4  | 996

What I am trying to query is for each deck, how many cards does it have that appear in more than 1 deck.

deckId  |  count(cardId)
--------------------
1  | 2 // 321 and 50 appear in both deckId=1 and deckId=2
2  | 2 // 321 and 50 appear in both deckId=1 and deckId=2
3  | 1 // 50 appears in deckId=2 and deckId=3
4  | 0 // none of the cards in deckId=4 appear in any other decks

I’ve tried

SELECT deckId, COUNT(cardId)
FROM table
GROUP BY deckId

but it just gives me (because each deck has 4 cards):

deckId  | count(cardId)
-----------------------
1 | 4
2 | 4
3 | 4
4 | 4

So how do I query for each deck, how many “common” cards does it have? (window functions were suggested, but i’m forced to use SQLite version 3.24 before window functions are implemented)

Answer

You can use window functions and aggregation:

select 
    deckId,
    sum(case when cnt > 1 then 1 else 0 end) cnt
from (
    select deckId, count(*) over(partition by cardId) cnt
    from mytable 
) t
group by deckId

Demo on DB Fiddle:

deckId | cnt
:----- | --:
1      |   2
2      |   2
3      |   1
4      |   0

---

In versions of SQLite that do not support window functions, you can emulate the window count with a subquery:

select 
    deckId,
    sum(case when cnt > 1 then 1 else 0 end) cnt
from (
    select 
        deckId, 
        (select count(*) from mytable t1 where t1.cardId = t.cardId) cnt
    from mytable t 
) t
group by deckId