MySQL: how to get duplicate rows x times

Question

What MySQL query should I use to get only rows repeated X times?

Example (stupid one ^^):

John likes apples.
Dave likes apples.
Bough likes bananas.
Light likes apples.
Wiky likes bananas.

I need a query that returns only fruits that 3 people like (apples in my example). Hooray!

Answer 1

I need a query that returns only fruits loved by 3 people (apples in my example)

Try the following:

SELECT fruit_name FROM `table` group by fruit_name having count(fruit_name)>=3;

Answer 2

if the “address” is the column by which you determine if the two rows are the same, then:

select address, count(address) as cnt from mailing_list group by address having cnt = $x order by cnt;

Answer 3

:

select count(*), column1 from table group by column

, , . , .

select count(*), column1, column2 from table group by column1,column2

EDIT:

X :

select c, column1 from (select count(*) as c, column1 from table group by column1) where c = X