SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there a more efficient way to write this...?

    I'm tallying votes and then coming up with the percentage of total votes for the "rating value".
    Code MySQL:
    SELECT (SELECT COUNT(rating) FROM exp_ratings) AS total_count
    	   ,COUNT(rating) AS vote_count
    	   ,(SELECT COUNT(rating) FROM exp_ratings WHERE rating ='5')/(SELECT COUNT(rating) FROM exp_ratings) AS avg 
      FROM exp_ratings
     WHERE rating ='5'
    I actually have 5 different rating values and have planned to run one query for each. Not sure if there is a way to do this any differently.

    Also, the # produced for "avg" has a leading zero followed by a decimal. Is there a way to strip that from the result?

    Thanks

    Edit: I think this answers my last question:
    Code MySQL:
    SELECT COUNT(rating) AS vote_count
          ,((SELECT COUNT(rating) FROM exp_ratings WHERE rating ='5')/(SELECT COUNT(rating) FROM exp_ratings))*100 AS avg
      FROM exp_ratings
     WHERE rating ='5'
    (don't need the initial sub-query shown in the first example)
    Still wonder if there is a more efficient way to write this?

  2. #2
    SitePoint Addict aguroyz's Avatar
    Join Date
    Jan 2009
    Location
    Konoha Fire Country
    Posts
    311
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hothousegraphix View Post
    I'm tallying votes and then coming up with the percentage of total votes for the "rating value".
    Code MySQL:
    SELECT (SELECT COUNT(rating) FROM exp_ratings) AS total_count
    	   ,COUNT(rating) AS vote_count
    	   ,(SELECT COUNT(rating) FROM exp_ratings WHERE rating ='5')/(SELECT COUNT(rating) FROM exp_ratings) AS avg 
      FROM exp_ratings
     WHERE rating ='5'
    I actually have 5 different rating values and have planned to run one query for each. Not sure if there is a way to do this any differently.

    Also, the # produced for "avg" has a leading zero followed by a decimal. Is there a way to strip that from the result?

    Thanks

    Edit: I think this answers my last question:
    Code MySQL:
    SELECT COUNT(rating) AS vote_count
          ,((SELECT COUNT(rating) FROM exp_ratings WHERE rating ='5')/(SELECT COUNT(rating) FROM exp_ratings))*100 AS avg
      FROM exp_ratings
     WHERE rating ='5'
    (don't need the initial sub-query shown in the first example)
    Still wonder if there is a more efficient way to write this?

    eyy did this query work??

  3. #3
    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 vote_count_1
         , 100.0 * vote_count_1 / total_ratings AS pct_rating_1
         , vote_count_2
         , 100.0 * vote_count_2 / total_ratings AS pct_rating_2
         , vote_count_3
         , 100.0 * vote_count_3 / total_ratings AS pct_rating_3
         , vote_count_4
         , 100.0 * vote_count_4 / total_ratings AS pct_rating_4
         , vote_count_5
         , 100.0 * vote_count_5 / total_ratings AS pct_rating_5
      FROM (
           SELECT SUM( CASE WHEN rating = 1 
                            THEN 1 ELSE 0 END ) AS vote_count_1  
                , SUM( CASE WHEN rating = 2 
                            THEN 1 ELSE 0 END ) AS vote_count_2  
                , SUM( CASE WHEN rating = 3 
                            THEN 1 ELSE 0 END ) AS vote_count_3  
                , SUM( CASE WHEN rating = 4 
                            THEN 1 ELSE 0 END ) AS vote_count_4  
                , SUM( CASE WHEN rating = 5 
                            THEN 1 ELSE 0 END ) AS vote_count_5  
                , COUNT(rating)                 AS total_ratings 
             FROM exp_ratings
           ) AS d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I appologize for my delayed response.

    @aguroyz - yeah, it actually dose spit out the values I'm looking for. This is certainly not my area of expertise but I certainly have no problem rolling up my sleeves and getting my hands dirty. I realize that what I posted was not the best approach but I did think it was enough to get across what I need to do.

    @937 - thank you very much. This works like a treat. I do have a few questions so I can better understand what your doing.

    For the most part, I understand the concept but I'm confused about the "THEN" portion of each CASE. Could you explain what that's doing?
    Code MySQL:
    SUM( CASE WHEN rating = 1 
                            THEN 1 ELSE 0 END ) AS vote_count_1

    Again, thanks.

  5. #5
    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)
    i'm not sure how to explain CASE WHEN THEN

    it's like an "IF" function

    when rating is equal to 1, then the CASE expression returns 1

    when rating is not equal to 1, then the CASE expression returns 0

    so the CASE expressions returns a bunch of 1s and 0s -- and the number of 1s is exactly equal to the number of rows that had rating = 1

    so when SUM adds up these 1s and 0s, you get the total number of rows that had rating = 1

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It does. Thank you for taking a moment of your time to explain.

    Regards.


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
  •