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. Link to comment https://forums.phpfreaks.com/topic/228632-phpmysql/ 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. Link to comment https://forums.phpfreaks.com/topic/228632-phpmysql/#findComment-1178815 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. Link to comment https://forums.phpfreaks.com/topic/228632-phpmysql/#findComment-1178873 Share on other sites More sharing options...
c_pattle Posted February 23, 2011 Author Share Posted February 23, 2011 Great! Thanks for your help. Link to comment https://forums.phpfreaks.com/topic/228632-phpmysql/#findComment-1178925 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.