SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Ankara
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    average of last 10 ratings for a user

    Hi,
    I am trying to calculate the average of last 10 rows for a user in a rating system. My table looks like this :

    PHP Code:
    CREATE TABLE `ratings` (
      `
    rating_idint(10unsigned NOT NULL auto_increment,
      `
    user_idint(10unsigned NOT NULL default '0',
      `
    rating_avgsmallint(5unsigned NOT NULL default '0',
      `
    post_datetimestamp NULL default CURRENT_TIMESTAMP,

    SELECT AVG(rating_avg) FROM ratings WHERE user_id="1"
    works fine for a single user but I need to select only the last 10 ratings of a selected user.

    I was wondering if this could be done in a single query ?

    Thanks in advance
    Irfan

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select avg(rating_avg) 
      from (
           select rating_avg
             from ratings 
            where user_id = 1 
           order by post_date desc
           limit 10
           ) as topten
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Ankara
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks r937, I had not thought of solving by a subquery. I guess I have to spare some more time to get used to new features in recent mysql versions like subqueries

  4. #4
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Ankara
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy,

    It was a sheer coincidence that I had visited your site r937.com just a few weeks ago and made a note of your nice collection of SQL links on your page SQL > SQL Links

    If it wouldn't be too much, I would like to ask if there's a way to calculate the average only if the ratings are more than 10 rows. I mean to calculate the average rating only and only if the inner subquery returns > 10 rows while still calculating the average for the last 10 records.

    I can do it in application level [PHP] and I have already done this by sending a query and calculating the number of rows but I guess doing this in the sql layer is more logical and efficient.

    Thanks for your time
    Irfan

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select avg(rating_avg)
      from (
           select rating_avg
             from ratings 
            where user_id = 1 
           order by post_date desc
           limit 10
           ) as topten
    having count(*) >= 10
    count(*) >= 10 for very large values of 10.
    Last edited by longneck; Aug 16, 2005 at 07:52.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select avg(rating_avg) 
      from (
           select rating_avg
             from ratings 
            where user_id = 1 
           order by post_date desc
           limit 10
           ) as topten
     having count(*) = 10
    classic example of HAVING without GROUP BY



    Edit:

    i didn't see longneck's when i posted mine -- i like his better
    Last edited by r937; Aug 16, 2005 at 07:09.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Ankara
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy,

    You're a life saver


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
  •