c_pattle Posted February 23, 2011 Share Posted February 23, 2011 I have a table that has a field called rating which can be from 0-100. I want to create a mysql query that will count the number of ratings between 0-20 then 21-40 then 41-60 etc. However I'm not really sure how to do this so any help would be great. Quote Link to comment Share on other sites More sharing options...
c_pattle Posted February 23, 2011 Author Share Posted February 23, 2011 btw I know how to do this for one of the criteria such as 0-20 by doing "COUNT(*) from reviews where review_rating between 0 and 20" but I don't know how to do this for all of the criteria I want in the same query. Quote Link to comment Share on other sites More sharing options...
btherl Posted February 23, 2011 Share Posted February 23, 2011 You can do it in one query with either of these methods: SELECT (SELECT count(*) FROM reviews WHERE review_rating BETWEEN 0 AND 20) AS zero_to_twenty, (SELECT count(*) FROM reviews WHERE review_rating BETWEEN 21 AND 40) AS twentyone_to_forty But that's really 5 queries all stuck together into one. SELECT SUM(CASE WHEN review_rating BETWEEN 0 AND 20 THEN 1 ELSE 0 END) AS zero_to_twenty, SUM(CASE WHEN review_rating BETWEEN 21 AND 40 THEN 1 ELSE 0 END) AS twentyone_to_forty FROM reviews This method is truly one query, and pretty much guarantees you'll only look at the table once. Quote Link to comment Share on other sites More sharing options...
c_pattle Posted February 23, 2011 Author Share Posted February 23, 2011 Great! Thanks for your help. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.