MySQL - Selecting rows with a minimum number of occurences

Question

Hi all,

I have this query:

SELECT DISTINCT brand_name FROM masterdata WHERE in_stock = '1' ORDER BY brand_name

It works well, except that I get far too many results. How do I limit this such that rather than just looking for distinct entries, it will only give me distinct entries that exist a minimum of 3 times (for example)?

Basically, if the column had this data...

brand_name
==========
apple
banana
apple
apple
orange
banana
orange
orange

...my current query would return "apple, banana, orange". How do I get it such that it only returns "apple, orange" (ignoring banana because it has less than three occurrences)?

I'm using PHP to build the query, if it matters.

Thanks!

Answer 1

Something like this (off-the-cuff and untested):

SELECT brand_name
  FROM masterdata
 WHERE in_stock = '1'
 GROUP BY brand_name
HAVING COUNT(*) >= 3
 ORDER BY brand_name