Aggregate and Numeric Functions - Learning MySQL and MariaDB - page 289

| backyard | 14 | 0.1628 |
+--------------+-------+---------+
In this example, we're dividing the number of votes for each choice by the variable con-
taining the total number of votes. That gives us numbers with four decimal places. Let's
change those numbers to read as percentages by multiplying them by 100 and usingthe
ROUND() function to get rid of the decimals. We'lluse CONCAT() to paste a percent
sign to the end of the number:
SELECT COLUMN_GET(choices, answer AS CHAR)
AS 'Birding Site',
COUNT(*) AS 'Votes',
CONCAT( ROUND( (COUNT(*) / @fav_site_total) * 100), '%')
AS 'Percent'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1
GROUP BY answer;
+--------------+-------+---------+
| Birding Site | Votes | Percent |
+--------------+-------+---------+
| forest | 30 | 35% |
| shore | 42 | 49% |
| backyard | 14 | 16% |
+--------------+-------+---------+
Notice that the ROUND() function rounded the first two numbers up and the last one
down. That's how rounding goes. Let's change the results to show one decimal place:
SELECT COLUMN_GET(choices, answer AS CHAR)
AS 'Birding Site',
COUNT(*) AS 'Votes',
CONCAT( ROUND( (COUNT(*) / @fav_site_total) * 100, 1), '%') AS
'Percent'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1
GROUP BY answer;
+--------------+-------+---------+
| Birding Site | Votes | Percent |
+--------------+-------+---------+
| forest | 30 | 34.9% |