Practical 5: Math Functions

Chapter Forty-Three

Syllabus topic Module 2, Practical 5: "Queries involving Math Functions"

Pages 161 to 164 of 206

Aim

To write queries involving math functions.

Rounding, three different ways

SELECT ROUND(516.3125, 2)    AS rounded,
       TRUNCATE(516.3125, 2) AS truncated,
       FLOOR(516.3125)       AS floored,
       CEIL(516.3125)        AS ceiled;

+---------+-----------+---------+--------+
| rounded | truncated | floored | ceiled |
+---------+-----------+---------+--------+
|  516.31 |    516.31 |     516 |    517 |
+---------+-----------+---------+--------+

Function	Does
`ROUND(x, d)`	rounds to d decimal places, up or down, at the halfway point away from zero
`TRUNCATE(x, d)`	cuts off after d decimal places, never rounding
`FLOOR(x)`	the largest whole number not greater than x
`CEIL(x)`, `CEILING(x)`	the smallest whole number not less than x

For a positive number, TRUNCATE(x, 0) and FLOOR(x) give the same answer, which is why students think they are the same function. They are not, and a negative number proves it:

SELECT ROUND(-7.6)    AS rounded,
       TRUNCATE(-7.6, 0) AS truncated,
       FLOOR(-7.6)    AS floored,
       CEIL(-7.6)     AS ceiled;

+---------+-----------+---------+--------+
| rounded | truncated | floored | ceiled |
+---------+-----------+---------+--------+
|      -8 |        -7 |      -8 |     -7 |
+---------+-----------+---------+--------+

Read that row carefully.

TRUNCATE throws the fraction away, so minus 7.6 becomes minus 7: it moves towards zero.

FLOOR goes down, so minus 7.6 becomes minus 8: it moves away from zero for a negative number.

ROUND goes to the nearest, which is minus 8 here.

CEIL goes up, which is minus 7.

That table is the answer to "distinguish between ROUND, TRUNCATE, FLOOR and CEIL", and the negative example is what makes the answer convincing.

ROUND with no second argument rounds to a whole number. ROUND(x, -1) rounds to the nearest ten, which is occasionally exactly what a report wants.

Rounding a real column

SELECT subject,
       AVG(price)            AS raw_average,
       ROUND(AVG(price), 2)  AS to_paise,
       ROUND(AVG(price))     AS to_rupees
FROM book GROUP BY subject ORDER BY subject LIMIT 3;

+-------------+-------------+----------+-----------+
| subject     | raw_average | to_paise | to_rupees |
+-------------+-------------+----------+-----------+
| Databases   |  499.833333 |   499.83 |       500 |
| Mathematics |  430.000000 |   430.00 |       430 |
| Networks    |  540.750000 |   540.75 |       541 |
+-------------+-------------+----------+-----------+

AVG gives more decimal places than anybody wants; ROUND is what makes it a price again.

Absolute value, sign and remainder

SELECT ABS(-42)      AS absolute,
       SIGN(-42)     AS sign_of_it,
       SIGN(0)       AS sign_of_zero,
       MOD(17, 5)    AS remainder,
       17 % 5        AS same_thing,
       17 DIV 5      AS whole_part;

+----------+------------+--------------+-----------+------------+------------+
| absolute | sign_of_it | sign_of_zero | remainder | same_thing | whole_part |
+----------+------------+--------------+-----------+------------+------------+
|       42 |         -1 |            0 |         2 |          2 |          3 |
+----------+------------+--------------+-----------+------------+------------+

ABS is [Practical 4(c): sqrt and abs, and the Headers They Need] in SQL, and MOD is the % of [Practical 1(c): Is This a Leap Year?]. % and MOD are the same operator written two ways, and DIV is integer division: the whole part with the remainder thrown away.

Practical 5: Math Functions

So the leap year rule of Module 1 is one expression here:

SELECT y AS year,
       (MOD(y, 400) = 0 OR (MOD(y, 4) = 0 AND MOD(y, 100) <> 0)) AS is_leap
FROM (SELECT 2000 AS y UNION SELECT 1900 UNION SELECT 2024
      UNION SELECT 2023) AS years
ORDER BY y;

+------+---------+
| year | is_leap |
+------+---------+
| 1900 |       0 |
| 2000 |       1 |
| 2023 |       0 |
| 2024 |       1 |
+------+---------+

The same four answers as the C program gave, which is the point: the rule is the rule, and the language only changes how it is written.

Powers and roots

SELECT POW(2, 10)   AS two_to_the_ten,
       POWER(3, 4)  AS three_to_the_four,
       SQRT(144)    AS root_of_144,
       ROUND(SQRT(2), 4) AS root_of_2,
       EXP(1)       AS e,
       ROUND(PI(), 5) AS pi;

+----------------+-------------------+-------------+-----------+-------------------+---------+
| two_to_the_ten | three_to_the_four | root_of_144 | root_of_2 | e                 | pi      |
+----------------+-------------------+-------------+-----------+-------------------+---------+
|           1024 |                81 |          12 |    1.4142 | 2.718281828459045 | 3.14159 |
+----------------+-------------------+-------------+-----------+-------------------+---------+

POW and POWER are the same function. SQRT of a negative number gives NULL rather than an error, which is worth knowing: the row survives and the value is missing.

The largest and smallest of several values

SELECT GREATEST(3, 17, 8)   AS biggest,
       LEAST(3, 17, 8)      AS smallest,
       GREATEST(-4, 0)      AS not_negative;

+---------+----------+--------------+
| biggest | smallest | not_negative |
+---------+----------+--------------+
|      17 |        3 |            0 |
+---------+----------+--------------+

GREATEST and LEAST are not MAX and MIN. These compare several values in one row; MAX and MIN from [Practical 4: Aggregate Functions, GROUP BY and HAVING] compare one value down many rows. Confusing the two pairs is a standing examination mistake, and the distinction is worth writing out:

	`GREATEST(a, b, c)`	`MAX(col)`
Compares	several columns or values, across one row	one column, across many rows
Is	an ordinary function	an aggregate function
Needs a `GROUP BY`	no	only to group the rows
Result	one value per row	one value per group

GREATEST(x, 0) was used in [Practical 5: Date Functions] to stop a fine going negative, and that is its commonest real use.

A worked example: the fine, in whole rupees

The library charges two rupees a day, rounded up to a whole rupee, on a fourteen day loan.

SELECT l.loan_id, b.title,
       DATEDIFF(l.returned_on, DATE_ADD(l.issued_on, INTERVAL 14 DAY)) AS late,
       CEIL(GREATEST(DATEDIFF(l.returned_on,
            DATE_ADD(l.issued_on, INTERVAL 14 DAY)), 0) * 2) AS fine
FROM loan l JOIN book b ON b.book_id = l.book_id
WHERE l.returned_on IS NOT NULL
ORDER BY fine DESC, l.loan_id;

+---------+------------------+------+------+
| loan_id | title            | late | fine |
+---------+------------------+------+------+
|       2 | Database Systems |   14 |   28 |
|       1 | The C Language   |   -2 |    0 |
|       3 | MySQL Reference  |   -4 |    0 |
|       5 | The C Language   |   -4 |    0 |
|       6 | Learn SQL        |   -3 |    0 |
+---------+------------------+------+------+

Practical 5: Math Functions

Three functions from three different chapters in one expression, which is what a real query looks like.

Formatting a number for a person

SELECT price,
       FORMAT(price, 2)          AS with_commas,
       CONCAT('Rs ', FORMAT(price, 2)) AS as_money
FROM book ORDER BY price DESC LIMIT 3;

+--------+-------------+-----------+
| price  | with_commas | as_money  |
+--------+-------------+-----------+
| 720.50 | 720.50      | Rs 720.50 |
| 655.25 | 655.25      | Rs 655.25 |
| 610.00 | 610.00      | Rs 610.00 |
+--------+-------------+-----------+

FORMAT(x, d) rounds to d places and puts separators in. Like DATE_FORMAT, it returns text, so it belongs at the very end of a query and never in a column.

Random numbers

RAND() gives a different value every time it is called, so its result is not printed in this book. It really runs:

SELECT book_id, title FROM book ORDER BY RAND() LIMIT 1;

ORDER BY RAND() LIMIT 1 picks a row at random, which is how a quiz question or a book of the day is chosen. On a large table it is slow, because every row gets a random number before the sort.

The functions in this practical

Function	Does
`ROUND(x, d)`	round to d places
`TRUNCATE(x, d)`	cut off at d places, towards zero
`FLOOR(x)`	down to a whole number
`CEIL(x)`	up to a whole number
`ABS(x)`	size without the sign
`SIGN(x)`	minus 1, 0 or 1
`MOD(a, b)`, `a % b`	remainder
`a DIV b`	whole part of the division
`POW(a, b)`, `POWER(a, b)`	a to the power b
`SQRT(x)`	square root; NULL for a negative x
`EXP(x)`, `LOG(x)`, `PI()`	the usual mathematics
`GREATEST(...)`, `LEAST(...)`	across one row
`FORMAT(x, d)`	text, with separators
`RAND()`	a random value between 0 and 1

What beginners get wrong

Thinking TRUNCATE and FLOOR are the same. They agree on positive numbers only.

Confusing GREATEST with MAX. One works across a row, the other down a column.

Storing a FORMAT result. It is text and cannot be summed.

Expecting SQRT(-1) to be an error. It is NULL.

Writing TRUNCATE(x) with no second argument. It needs both.

Rounding at every step of a calculation. Round once, at the end, or the small errors add up.

Using ORDER BY RAND() on a large table. It sorts the whole table.

Quick revision

ROUND(x, d) to the nearest; TRUNCATE(x, d) cuts towards zero; FLOOR down; CEIL up.
On a negative number all four differ: minus 7.6 gives minus 8, minus 7, minus 8 and minus 7.
ABS, SIGN, MOD(a, b) which is also a % b, and DIV for the whole part.
POW(a, b) and SQRT(x), which is NULL for a negative x.
GREATEST and LEAST work across one row; MAX and MIN work down many rows.
GREATEST(x, 0) is how a value is stopped from going negative.
FORMAT(x, d) makes text; keep it out of stored columns.
ORDER BY RAND() LIMIT 1 picks a random row and is slow on a large table.

Practical 5: Math Functions

What goes in your journal

Aim, then one query holding all four rounding functions applied to the same positive number, and a second holding them applied to the same negative number. Those two rows side by side are this practical's whole content, and an examiner looking at them can see at once whether you know the difference.

Then ABS, MOD, POW and SQRT in one query, and the fine calculation as a worked example.

Test yourself

1. What do ROUND, TRUNCATE, FLOOR and CEIL give for minus 7.6? Minus 8, minus 7, minus 8 and minus 7.

2. When do TRUNCATE(x, 0) and FLOOR(x) agree? When x is positive or zero. For a negative x, TRUNCATE moves towards zero and FLOOR moves away from it.

3. What is the difference between GREATEST and MAX? GREATEST compares several values within one row and is an ordinary function. MAX compares one column across many rows and is an aggregate.

4. What does SQRT(-4) return? NULL, not an error.

5. How do you make a value that might be negative come out as zero instead? GREATEST(x, 0).

6. Why should FORMAT not be used in a stored column? Because it returns text with separators in it, which cannot be summed, compared as a number or sorted numerically.

The rest of this subject

These notes are cut from the University's printed syllabus. Open the syllabus itself for the same subject.

Syllabus for Major Practical 1

Report or request