Rounding

In the previous exercise, 10 / 3.0 gave 3.33333333333333. Nobody wants to pay $13.3333333333333, so prices need rounding, and for that, SQL has a function.

A function is a small ready-made program: you give it values, and it gives back a result. You write its name and after it, in parentheses, the values it should work with, its arguments, separated by commas. In ROUND(10 / 3.0, 2), the function ROUND gets two arguments, 10 / 3.0 and 2, and gives back 3.33. SQLite has dozens of functions, and in this lesson, you'll learn the most useful ones. Their names don't depend on case (round works too), and this course writes most of them in capitals, like keywords.

ROUND(x, digits) rounds the number x to the given number of decimal places. With one argument, ROUND(x) rounds to a whole number. A half is rounded up, away from zero: 2.5 becomes 3.0 and -2.5 becomes -3.0.

SELECT ROUND(3.14159, 2) AS pi, ROUND(2.5) AS half, ROUND(12.5, 2) AS cents;

Two things to notice. ROUND always gives a decimal number, even when it rounds to a whole one: 3.0, not 3. And it doesn't add zeros at the end: 12.5 rounded to two places stays 12.5, not 12.50.

A function can be used wherever a value can: in SELECT next to columns, or in a calculation. Its arguments can be columns and whole calculations too. Let's say a euro costs $1.17. These are the prices in euros, rounded to cents:

SELECT title, price, ROUND(price / 1.17, 2) AS euros
FROM games
LIMIT 3;

SQLite works from the inside out: first it divides the price by 1.17, then it rounds the result. As with any calculation, name the result with AS; otherwise, the column is called by the whole call, ROUND(price / 1.17, 2).

Another function for numbers is ABS(x), the absolute value: the number without its minus sign. ABS(-5) and ABS(5) are both 5. It's handy when only the size of a difference matters, for example how far a game's price is from $30, whether above or below: ABS(price - 30).

Typical mistake: rounding the result of a whole division. ROUND(price / 3, 2) gives 13.0 for a $40 game, not 13.33, because 40 / 3 is 13 before ROUND even gets it. Rounding can't bring back what the division cut off, so divide by 3.0.