CODEWITHSUNDEEP
mysqlsql
TheLettuceMaster
TheLettuceMaster

Reputation: 15734

Find out AVG column in SQL

I have this php/sql Query: 

$result = mysql_query("
            SELECT r.item_id, AVG(rating) AS avgrating, count(rating) AS count, i.item, c.category
            FROM ratings AS r
            LEFT JOIN items AS i
            ON r.item_id = i.items_id
            INNER JOIN master_cat c
            ON c.cat_id = i.cat_id
            GROUP BY item_id 
            ORDER BY avgrating DESC 
            LIMIT 25;");

When I output this, count is correct, it shows how much votes certain items have received.

I simply want to add a WHERE count >= 10 clause but everything breaks. Obviously, when there are thousands of items, some will get one vote and have 100%. But that is not a good indicator. I want to print out items that have at least 10 votes (or count >= 10)

Upvotes: 1

Views: 57

Answers (3)

Hip Hip Array
Hip Hip Array

Reputation: 4753

you need to tell it what you want to count

having count(*) > 10

Upvotes: 1

AA.
AA.

Reputation: 4606

You should to use having instead where

SELECT 
    r.item_id, AVG(rating) AS avgrating, 
    count(rating) AS count, i.item, c.category
FROM 
    ratings AS r
    LEFT JOIN items AS i
        ON r.item_id = i.items_id
    INNER JOIN master_cat c
        ON c.cat_id = i.cat_id
GROUP BY 
    item_id 
HAVING
    count >= 10
ORDER BY 
    avgrating DESC 
LIMIT 25;

Upvotes: 2

Marc B
Marc B

Reputation: 360592

You can't use a where filter on the results of an aggregate function (count()). where is applied at the row-level, as the DB is deciding whether to include the row or not in the result set - at this point the results of the count aren't available yet.

What you want is a having clause, which is applied as one of the last steps before results are sent to the client, after all the aggregate results have been calculated.

...
GROUP BY item_id
HAVING count > 10
ORDER BY ...

Upvotes: 1

Related Questions