Possible to count number of occurrences in a "group" in MySQL?

Sorry if the title is misleading, I don’t really know the terminology for what I want to accomplish. But let’s consider this table:

CREATE TABLE entries (
  id INT NOT NULL,
  number INT NOT NULL
);

Let’s say it contains four numbers associated with each id, like this:

id number
1  0
1  9
1  17
1  11
2  5
2  8
2  9
2  0
.
.
.

Is it possible, with a SQL-query only, to count the numbers of matches for any two given numbers (tuples) associated with a id?

Let’s say I want to count the number of occurrences of number 0 and 9 that is associated with a unique id. In the sample data above 0 and 9 does occur two times (one time where id=1 and one time where id=2). I can’t think of how to write a SQL-query that solves this. Is it possible? Maybe my table structure is wrong, but that’s how my data is organized right now.

I have tried sub-queries, unions, joins and everything else, but haven’t found a way yet.

>Solution :

You can use GROUP BY and HAVING clauses:

SELECT COUNT(s.id)
FROM(
     SELECT t.id
     FROM YourTable t
     WHERE t.number in(0,9)
     GROUP BY t.id
     HAVING COUNT(distinct t.number) = 2) s

Or with EXISTS():

SELECT COUNT(distinct t.id)
FROM YourTable t
WHERE EXISTS(SELECT 1 FROM YourTable s
             WHERE t.id = s.id and s.id IN(0,9)
             HAVING COUNT(distinct s.number) = 2)