I have a table with column “Score”, which holds a number ranging from 1 to 10. I need to achieve the following calculation in MySQL:
x
Column: Score
Row 1: 10
Row 2: 9
Row 3: 9
Row 4: 7
Row 5: 10
Calculation: (Count of all scores >= 8) / (Count of all scores)
Calculation: (4) / (5)
Result: 0.8
Result in percent: 80%
How can I achieve this in as little queries as possible to get the result of “80%”?
Advertisement
Answer
You could do that with this query:
SELECT SUM(Score >= 8) / COUNT(*) AS result,
SUM(Score >= 8) / COUNT(*) * 100 AS resultpercentage
FROM yourtable
It relies on the fact that MySQL treats boolean values as either 1 or 0 in a numeric context, so for every Score >= 8 we add one to the SUM.