Search code examples
mysqlsqlcountrelational-division

Count items in category A & B (MySQL)

I’m trying to alter something to make it work for me. I think my question is pretty easy to anwer for someone who knows SQL. I have the following table (two columns):

entry_id | cat_id
5        | 3
6        | 3
7        | 3
7        | 5
7        | 6
8        | 5
9        | 3
9        | 5

Now I want to count all entry_ids that are in cat_id 3 and 5 (they must be in both categories). What could I do to get "2" as a result (entries 7 and 9 makes a sum of 2).

Hope someone can help. Thanks!

Solution

  • This problem is sometimes called Relational Division

    SELECT  COUNT(*) totalCOunt
FROM
(
    SELECT  entry_id 
    FROM    tableName
    WHERE   cat_id IN (3, 5)
    GROUP   BY entry_id
    HAVING  COUNT(DISTINCT cat_id ) = 2
) s