Whole and Decimal Numbers

In lesson 2, you saw that 25 / 2 gives 12, not 12.5. This catch surprises almost everyone, so let's find out why it happens and what to do about it. You don't even need a table for that: a SELECT without FROM just calculates what you give it, so you can use it as a calculator:

SELECT 7 / 2;

Not 3.5, but 3. SQLite has two kinds of numbers: whole numbers (the type INTEGER), such as 7 or 40, and decimal numbers (the type REAL), such as 3.5 or 40.0. When both numbers in a division are whole, the result is a whole number too, and whatever would come after the decimal point is cut off. Nothing is rounded up: 7 / 2 is 3, 9 / 10 is 0, and -7 / 2 is -3. (Mathematicians say that it's rounded toward zero.)

As soon as one of the two numbers is a decimal one, so is the result. So when you need the exact result, write one of the numbers with .0:

SELECT 7 / 2.0, 7.0 / 2;

This matters in Pixel Shop, because its prices are whole numbers (the column price is INTEGER). Here's half of each price, divided by 2 and by 2.0:

SELECT title, price, price / 2 AS half_whole, price / 2.0 AS half
FROM games
LIMIT 3;

Half of $15 is $7.50, but price / 2 says 7. Notice also how SQLite writes decimal numbers: 20.0 is a decimal number that happens to be whole, and the .0 tells you so. Long results are shown with up to 15 digits: 10 / 3.0 is 3.33333333333333. In the next exercise, you'll learn to round them.

Another way is to multiply by 1.0 first: price * 1.0 / 2. Multiplying by 1.0 doesn't change the value, it only makes it a decimal number. That's the way to go when both numbers of a division are columns, so there's no number you could add .0 to. But it has to come first: in price / 2 * 1.0, the division has already cut off the half, and you get 7.0.

The operator % gives the remainder of a whole division, what's left over. 135 minutes are 2 hours and 15 minutes:

SELECT 135 / 60 AS hours, 135 % 60 AS minutes;

So whole division isn't a mistake, it's another kind of division, and sometimes it's exactly what you need: hours and minutes, full boxes and the copies left over, or even and odd numbers (id % 2 is 0 for even IDs and 1 for odd ones). Note that % in SQL means the remainder, not percent.

Typical mistake: a percentage with whole numbers. 15 percent of a price isn't price / 100 * 15: for every price under $100, price / 100 is 0, so the result is 0 too. Write price * 15 / 100.0 or simply price * 0.15.