SitePoint Sponsor 

User Tag List
Results 1 to 6 of 6

Jan 4, 2009, 23:00 #1
 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:
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:
Still wonder if there is a more efficient way to write this?

Jan 5, 2009, 00:25 #2

Jan 5, 2009, 00:41 #3
 Join Date
 Jul 2002
 Location
 Toronto, Canada
 Posts
 39,340
 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

Jan 9, 2009, 12:10 #4
 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?
Again, thanks.

Jan 9, 2009, 12:14 #5
 Join Date
 Jul 2002
 Location
 Toronto, Canada
 Posts
 39,340
 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?

Jan 9, 2009, 13:30 #6
 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