MySQL query by count

Question

I've got a table that looks something like this:

CREATE TABLE `mailer__opens` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `idSubscriber` int(10) unsigned NOT NULL,
 `date` datetime NOT NULL,
 PRIMARY KEY  (`id`)
)

I'm trying to build a query which returns only the results where the value in idSubscriber is repeated 5 or more times. (I hope I'm explaining this right).

EG, if the data in the table looked like this:

id | idSubscriber | date
------------------------------
1  | 00001        | 2010-01-01
2  | 00002        | 2010-01-02
3  | 00001        | 2010-01-05
4  | 00003        | 2010-01-26
5  | 00004        | 2010-02-14
6  | 00001        | 2010-02-28
7  | 00002        | 2010-03-05
8  | 00001        | 2010-03-06
9  | 00003        | 2010-03-10
10 | 00001        | 2010-04-01
11 | 00004        | 2010-05-06
12 | 00002        | 2010-05-08

I'd be interested in records 1, 3, 6, 8 and 10, because the idSubscriber 00001 has 5 or more records.

Can anyone provide me with a query that would do this? Thank you.

Answer 1

To list the idSubscriber that has repeated five of more times you can use:

select idSubscriber 
from mailer__opens 
group by(idSubscriber) having count(*) >= 5;

To get the rows corresponding to such an idSubscriber you can use:

select * 
from mailer__opens 
where idSubscriber in 
  ( select idSubscriber 
    from mailer__opens 
    group by(idSubscriber) having count(*) >= 5 )

Answer 2

You must use GROUP BY with a HAVING clause:

SELECT id FROM mailer__opens GROUP BY idSubscriber HAVING COUNT(id) >= 5

Answer 3

First of all you need to get the different idSubscriber values:

SELECT idSubscriber
FROM `mailer__opens` 
GROUP BY idSubscriber
HAVING count( * ) >=5

For the given dataset, this will fetch only one value: 1

Then you need to select all rows where the idSubscriber is equal to those values. Therefore, your final query becomes:

SELECT * 
FROM mailer__opens
WHERE idsubscriber
IN (
    SELECT idSubscriber
    FROM `mailer__opens` 
    GROUP BY idSubscriber
    HAVING count( * ) >=5
)

Answer 4

SELECT id FROM mailer__opens WHERE idSubscriber IN (SELECT idSubscriber FROM mailer__opens GROUP BY idSubscriber HAVING COUNT(id) >= 5)