SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this possible?

    I have a table of entries with columns "total_rating" and "votes".

    total_rating is a sum of all ratings (from 0 to 5) submitted by users and votes is the number of people that have rated the entry.

    So, obviosly total_rating / votes = average rating

    I would like to select entries from the database ordered by their average ratings. I could do something like this:

    Code:
    SELECT * FROM table ORDER BY total_rating / votes
    But there are some entries users haven't voted on yet - which means division by zero.

    Is there some other way of doing this?

    I know I could add another column with average rating but I want to keep the current table.

    Richard

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    order by case when votes = 0 then -1 else total_rating/votes end desc

  3. #3
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you but why -1?

    Code:
    order by case when votes = 0 then -1 else total_rating/votes end desc

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    So that entries without a vote will come at the end. They will also come after those with a total rating of zero. Do you want it any other way? Just tell.

  5. #5
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, this works as I needed it, I just wanted to understand what -1 means in the case statement


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •