Hey guys I really am struggling with a MySql query, I have a table named 'info' and in it I have a column called 'rating' in it I have ratings between 1-10.
Now I need to generate a percentage value of how many ratings are from 1-6 and 7-8 and 9-10 but I need them to display desperately and after that I need a second query that can subtract the percentage value of the results from 1-6 and 9-10.
The query below is as close as I could get from all my research however I don't know how to get a percentage of ratings 1-6 only and not all of them and also how to get a second query to subtract the 1-6 and 9-10 rating percentages.
SELECT rating,
COUNT(*) AS Count,
(COUNT(*) / _total ) * 100 AS Percentege
FROM info,
(SELECT COUNT(*) AS _total FROM info) AS myTotal
GROUP BY rating
select if(rating between 1 and 6, '1-6',
if( rating between 7 and 8, '7-8',
'9-10' )
) as rating_range,
count(1) as num
from info
group by rating_range
Edit: Adding rounding and calculations This can be used as a subquery. Given the group by you need to get the total amount seperately:
select Q.rating_range,
Q.num,
round(Q.num * 100 / Q.total, 2) as percent
from (
select R.*,
(select count(1) from info) as total
from (
select if(rating between 1 and 6, '1-6',
if( rating between 7 and 8, '7-8',
'9-10' )
) as rating_range,
count(1) as num
from info
group by rating_range ) R
) Q
group by Q.rating_range
In terms of relative values, I would probably do that in my external application if I had one. Otherwise you could do a specific query I suppose:
select Q.rating_range,
Q.num,
round(Q.num * 100 / Q.total, 2) as percent,
round( (Q.num - Q.total_nine_ten) * 100 / Q.total, 2) as diff_from_nine_ten
from (
select R.*,
(select count(1) from info) as total,
(select count(1) from info where rating > 8 ) as total_nine_ten
from (
select if(rating between 1 and 6, '1-6',
if( rating between 7 and 8, '7-8',
'9-10' )
) as rating_range,
count(1) as num
from info
group by rating_range ) R
) Q
group by Q.rating_range
Not very elegant, but works