Database Reference
In-Depth Information
| shore | 42 | 48.8% |
| backyard | 14 | 16.3% |
+--------------+-------+---------+
The
ROUND()
function rounded up and down to the first decimal place based on the true
value, which includes multiple decimal places. Suppose we want to be conservative and
round all values down, or all values up. For that, we need otherfunctions.
Rounding Only Down or Up
To round only down,use the
FLOOR()
function. To round only up, usethe
CEILING()
function. Let's use the previous example to see how we would round down the results:
SELECT COLUMN_GET(choices, answer AS CHAR)
AS 'Birding Site',
COUNT(*) AS 'Votes',
CONCAT( FLOOR( (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 | 34% |
| shore | 42 | 48% |
| backyard | 14 | 16% |
+--------------+-------+---------+
In this example, we replaced
ROUND()
with
FLOOR()
so that the results would be roun-
ded down. The
FLOOR()
function doesn't allow for specifying the number of decimal
places. Instead, it rounds down to the integer value.
If we want to round only up, we would use the
CEILING()
function like so:
SELECT COLUMN_GET(choices, answer AS CHAR)
AS 'Birding Site',
COUNT(*) AS 'Votes',
CONCAT( CEILING( (COUNT(*) / @fav_site_total) * 100), '%') AS
'Percent'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1
