mysql select distinct row, that appears at least (n) times

Question

I am very new to SQL, and I am using MySQL, and I have a table that holds blogs. In that table, I have a column named "blog_category". I am trying to return DISTINCT values from the blog_category that appear at least (n) times. Example:

    blog_category
    -------------
    category_1
    category_1
    category_2
    category_3
    category_3
    category_4
    category_4
    category_1
    category_1
    category_2
    category_2

I only want to return the categories that appear say, at least 3 times (greater than or equal to 3).

I can get it to display the ones that appear more than just once with the DISTINCT keyword, but I only want the ones that actually appear more than the specified amount of times, in this case just the ones that appear at least than 3 times.

So in this case I only want to return "category_1" and "category_2".

I have tried numerous ways, but nothing seems to work, I keep getting the following error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean
given in [FILE NAME OMITTED] on line 101 

Here are some of the things I've tried:

$query = "SELECT DISTINCT blog_category 
          FROM blog_posts 
          HAVING COUNT (blog_category) >= 3";

$query = "SELECT DISTINCT blog_category 
          FROM blog_posts 
          WHERE blog_category IN
          (SELECT blog_category
          HAVING (COUNT (blog_category) >= 3))";

$query = "SELECT DISTINCT blog_category 
          FROM blog_posts 
          WHERE blog_category 
          HAVING COUNT(blog_category) >= 3";

$query = "SELECT DISTINCT blog_category 
          FROM blog_posts 
          WHERE COUNT(blog_category) >= 3";

BELOW ARE SUGGESTED ANSWERS FROM OTHER USERS, THAT I HAVE TRIED AND HAD NO LUCK WITH.....

$query = "SELECT blog_category 
          FROM blog_posts 
          GROUP BY blog_category
          HAVING COUNT(*) >= 3";

HERE IS THE FULL SECTION OF CODE BEING RAN (I tested straight on the SQL test part of the server and it works just fine, but when I run the actual full code it keeps returning that the query is false) :

$query = "SELECT blog_category 
        FROM blog_posts
        GROUP BY blog_category
        HAVING COUNT(*) > 2)";

        $result = mysqli_query( $conn, $query );

        if( mysqli_num_rows( $result ) > 0 ) {
            while( $row = mysqli_fetch_assoc($result) ) {
                echo "<ul>";
                echo "<li>" . $row['blog_category'] . "</p>";
                echo "</ul>";           
            }
        }

Answer 1

You need GROUP BY:

SELECT blog_category 
FROM blog_posts 
GROUP BY blog_category
HAVING COUNT(*) >= 3;

Basically, you should never be using SELECT DISTINCT with aggregation functions (such as COUNT(*)). Whenever you have functions such as COUNT(), MIN(), MAX(), and so on, you should either have a GROUP BY or be expecting exactly one row in the result set.

Admittedly, there are exceptions. In some databases, you might do this with analytic functions that don't have aggregation equivalents. That does not apply to MySQL.

Answer 2

You have right idea with a having clause, but you're missing a group by clause to apply the count per group (i.e., per distinct value of blog_category):

SELECT   blog_category 
FROM     blog_posts 
GROUP BY blog_category
HAVING   COUNT(*) >= 3

Answer 3

Like this:

SELECT blog_category 
FROM blog_posts
GROUP BY blog_category
HAVING COUNT (blog_category) >= 3

You've got to GROUP BY so it knows how to count.