SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Performing multiple GROUP BY math calculations

    I need to add a function where users can rate books thumbs up for thumbs down. Thumbs up is logged as 1 (one) and thumbs down is logged as -1 (negative one).

    Here's the database structure:
    Code:
    CREATE TABLE book_rating
    (
        id      INT      UNSIGNED NOT NULL AUTO_INCREMENT,
        book_id SMALLINT UNSIGNED NOT NULL,
        rating  TINYINT           NOT NULL,
        PRIMARY KEY (id)
    ) ENGINE = MyISAM;
    
    CREATE TABLE book_rating_avg
    (
        book_id             SMALLINT UNSIGNED NOT NULL,
        thumbs_up_votes     SMALLINT UNSIGNED NOT NULL,
        thumbs_down_votes   SMALLINT UNSIGNED NOT NULL,
        thumbs_up_percent   TINYINT  UNSIGNED NOT NULL,
        thumbs_down_percent TINYINT  UNSIGNED NOT NULL,
        PRIMARY KEY (book_id)
    ) ENGINE = MyISAM;
    The "thumbs_down_votes" field would store the total number/SUM of -1 records from the "book_rating" table. The "thumbs_down_percent" field would store the percent of thumbs down votes compared to thumbs up votes. For instance, if there were 10 book ratings (3 negative, 7 positive) the columns would store for one book:
    Code:
    book_id:             999
    thumbs_up_votes:     7 
    thumbs_down_votes:   3 
    thumbs_up_percent:   70%  
    thumbs_down_percent: 30%
    I need to perform an INSERT...INTO or REPLACE...INTO operation that updates all 4 values in the "book_rating_avg" table. Is there any way to do this in one query instead of multiples?

  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 total_up   AS thumbs_up_votes
         , total_down AS thumbs_down_votes
         , ROUND(100.0*total_up
                      /total_ratings) AS thumbs_up_percent
         , ROUND(100.0*total_down
                      /total_ratings) AS thumbs_down_percent 
      FROM (
           SELECT book_id 
                , COUNT(*) AS total_ratings
                , COUNT(CASE WHEN rating = 1 
                             THEN 'up' END ) AS total_up
                , COUNT(CASE WHEN rating = -1 
                             THEN 'down' END ) AS total_down
             FROM book_rating
           GROUP
               BY book_id
           ) AS d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works perfectly. Thank you Rudy.


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
  •