Database Reference
In-Depth Information
Numeric Functions
Numeric functions arefunctions that change numbers in some way. They don't do a calcu-
lation, per se. That would be arithmetic functions. Instead, they help you simplify the nu-
meric result of a query. You might want to round a number up or down, or get the absolute
value. These actions can be done easily with numeric functions. We'll look at them in this
section.
Rounding Numbers
Computers are veryprecise, so when we ask them to do a calculation, they will sometimes
return a number with many decimal places. That may not matter to you, especially if the
number is not displayed and used just by other functions for processing, either now or later.
However, as humans, we tend to be more comfortable with rounded numbers. We're usu-
ally not as precise as computers. To that end, there are a few numeric functions that may be
used for rounding.
In
Dynamic Columns
, we created some tables with dynamic columns in MariaDB. These
included surveys of members about their bird-watching preferences. Let's use those tables
and the data they contain to test some numeric functions. If you didn't create those survey
tables or if you aren't using MariaDB, you won't be able to participate in these examples.
To start, let's look at one of the SQL statements we used in that section. We'll run it again,
but with more data from my site:
SELECT IFNULL(COLUMN_GET(choices, answer AS CHAR), 'total')
AS 'Birding Site', COUNT(*) AS 'Votes'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1
GROUP BY answer WITH ROLLUP;
+--------------+-------+
| Birding Site | Votes |
+--------------+-------+
| forest | 30 |
| shore | 42 |
| backyard | 14 |
| total | 86 |
+--------------+-------+
This shows us the number of votes from members for the types of locations they prefer for
watching birds. Let's calculate the percentages to go with these results. To do this, we need
