[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Average Rating, like Netflix
From:       Andy Shellam <andy-lists () networkmail ! eu>
Date:       2008-12-22 23:09:54
Message-ID: 49501E42.8090303 () networkmail ! eu
[Download RAW message or body]

Or you could wrap your entire SELECT in another query, and do an IFNULL 
around the rating field to convert it to 0 (or some other value 
important to you) as follows:

SELECT
    movie_id,
    <... any other fields from movies table you want ...>,
    IFNULL(ratings, 0) AS rating
FROM
(
    SELECT movies.*, average(ratings.rating) AS rating FROM movies
    LEFT JOIN ratings ON movies.movie_id=ratings.movie_id
    GROUP BY movies.movie_id
) result

Andy

Brent Baisley wrote:
> The ratings field would be NULL. You could also add a count in your
> query to tell how many ratings there were. If count is 0, you know
> there are no ratings.
> SELECT count(ratings.rating_id) AS rate_count, ...
>
> Brent Baisley
>
> On Mon, Dec 22, 2008 at 12:39 PM, Brian Dunning <brian@briandunning.com> wrote:
>   
>> If I did the left join to include movies with no ratings, how would I tell
>> if it had no ratings? If I used mysql_fetch_array in PHP, would
>> $result['rating'] == 0, or '', or NULL, or what?
>>
>> On Dec 22, 2008, at 9:29 AM, Brent Baisley wrote:
>>
>>     
>>> The biggest problem is your join condition (and no group by). It's
>>> fine for MySQLv4, but things have changed in v5. You should start
>>> getting in the habit of moving the join filters from the WHERE clause
>>> to a specific JOIN condition. Use the WHERE clause to perform filters
>>> after the join occurs.
>>> For example:
>>> SELECT movies.* average(ratings.rating) FROM movies
>>> INNER JOIN ratings ON movies.movie_id=ratings.movie_id
>>> GROUP BY movies.movie_id
>>>
>>> Change the INNER JOIN to a LEFT JOIN if you want all movies, even
>>> those with no ratings.
>>>
>>> Brent Baisley
>>>
>>>
>>> On Mon, Dec 22, 2008 at 11:13 AM, Brian Dunning <brian@briandunning.com>
>>> wrote:
>>>       
>>>> Pretend I'm Netflix and I want to return a list of found movies,
>>>> including
>>>> the average of related ratings for each movie. Something like this:
>>>>
>>>> select movies.*, average(ratings.rating) from movies, ratings where
>>>> movies.movie_id=ratings.movie_id
>>>>
>>>> I'm sure that's wrong in about 10 different ways but hopefully you get
>>>> what
>>>> I'm trying to do. Thanks.
>>>>         
>
>   

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]