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.

codaddict · Accepted Answer · 2010-11-19 11:05:30Z

3

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 )

edited Nov 19, 2010 at 11:05

answered Nov 19, 2010 at 11:00

codaddict

457k83 gold badges501 silver badges537 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Grossman Over a year ago

This won't work, it'll only give you one row back (all the rows for a given idSubscriber were collapsed into one per the GROUP BY), where he said he wants all those rows meeting that condition.

SirDarius · Accepted Answer · 2010-11-19 11:01:19Z

2

You must use GROUP BY with a HAVING clause:

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

answered Nov 19, 2010 at 11:01

SirDarius

43.3k8 gold badges92 silver badges106 bronze badges

2 Comments

Dan Grossman Over a year ago

This won't work, it'll only give you one row back (all the rows for a given idSubscriber were collapsed into one per the GROUP BY), where he said he wants all those rows meeting that condition.

SirDarius Over a year ago

right, the solution I would have given has been also given by codaddict anyways, so I won't repeat it here

Anax · Accepted Answer · 2010-11-19 11:03:34Z

0

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
)

answered Nov 19, 2010 at 11:03

Anax

9,4225 gold badges37 silver badges68 bronze badges

Comments

Dan Grossman · Accepted Answer · 2010-11-19 11:06:10Z

0

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

answered Nov 19, 2010 at 11:06

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Collectives™ on Stack Overflow

MySQL query by count

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related