SQL Math: Whole Numbers, Real Numbers

March 28, 2011 Beginner, Intermediate, sql server, sqlserverpedia-syndication, SSC, Tips, TSQL 2 Comments

It’s been a long time since grade school math, and for some the details have become kinda fuzzy. I’m no [insert name of famous math genius here], but I’ve been able to explain some basic things over the years. A common SQL Math Misunderstanding came up again recently with a fellow SQL practitioner, so off we go.

The Basics: Two Kinds of Numbers

There are (at least) two kinds of numbers: real numbers, and whole numbers.

Real:

1.2 is a real number. So is 1, and -4.3, and 0, and 24 1/2, and and π.

Plus and minus infinity, anything with or without a decimal or fraction is a real number.

Any number that could be used to measure or quantify anything in real life is a real number (e.g., 4 grams of flour, 1 1/4 hours till bedtime, 2,089.32 miles to Chicago, -14,259,891,873,204.32 in the U.S.A.’s bank account, etc.)

Whole:

1 is a whole number (also called integer). So is 0, and -4, and 43.

Plus and minus infinity, anything without a decimal or a fraction is a whole number.

So, whole numbers are a subset of real numbers.

Any number that could be used to count anything in real life (without using a decimal or fraction) is a whole number (e.g., I have 16,000 blue M&Ms, two cats, and I have -40 dollars in my bank account.)

I’m going to use the terms whole number and integer interchangeably for the rest of this article.

SQL Math: Division

Math – division, specifically – is slightly different for whole numbers and real numbers. If you open up Windows calculator and enter 6/600, you’ll get 0.01. The number 6 has exactly .01 (one hundredth) of 600 inside it. But if you open up SQL server and type in SELECT 6/600, you get 0. Why?

In SQL Server, every column, variable, and parameter requires a data type. When you pass a constant (like 6 or 600) to the SQL engine, it assigns a data type to that constant. SQL gives 6 and 600 the type INT, the integer data type. (For the full set of constant typing rules, see the SQL Server BOL article Constants.)

Let’s start with M&Ms. Our next division problem, 50/4, is made up of two whole numbers. Let’s assume we’re divvying up M&Ms, and phrase the problem 50/4 as “How many whole groups of 4 M&Ms can I make out of this bag of 50 M&Ms?” The answer is 12 (groups of 4 M&Ms). The question doesn’t care that we have 2 M&Ms left over. Integer math only cares about the number of groups of N items (where N is the number on the bottom of X/N) you can make.

Six Piddly M&Ms. 600? CAN NOT HAZ!

So back to our question: Why is the ansewr 6/600 zero instead of .01? Because 6 and 600 are integers, and the problem 6/600 is “How many groups of 600 can I make out of this bag of 6 M&Ms?” You can’t get even one group of 600 M&Ms out of a bag with 6 piddly M&Ms in it, so the answer is zero.

If you want the answer .01, then the question should be SELECT 6.0/600.0. This types the problem as real numbers (DECIMAL data type – though you could also use REAL or FLOAT), and it phrases the question as “Exactly how many whole and partial groups of 600 are in 6?” The answer, as we’ve seen, is .01, meaning “there is exactly one-one hundredth of a group of 600 within 6.” Or more traditionally, “I can divide 600 into 6 exactly .01 times.”

SQL Math: Modulus

A quick note on modulus – the remainder operator. Remember remainders? When dividing in grade school, we said 50/4 is 12 remainder 2, meaning after all the whole number division was done, there were still 2 (M&Ms) left over. In SQL we’d find out the remainder using SELECT 50%4, which results in 2. See?

SELECT 50/4 AS [Answer to 50/4], 50%4 AS [Answer to 50%4]

Results:

Answer to 50/4	Answer to 50%4
12	2

That’s easy enough. But what happens if we want the modulus for 6/600?

SELECT 6/600 AS [Answer to 6/600], 6%600 AS [Answer to 6%600]

Results:

Answer to 6/600	Answer to 6%600
0	6

We know wny 6/600 is zero. But why is 6 modulus 600 = 6? Because that’s what’s left over for this problem…600 went into 6 zero times, and we have a remainder of 6!

It sounds simple, but then, everything’s simple once you know the answer.

Finally: In mathematics, dividing real numbers results in no remainder, because we’re dealing with exact numbers. 6.0/600.0 is equal to .01, and there is nothing left over. BUT SQL Server does allow you to get the modulus (remainder) for real numbers: For example, SELECT 6.0%600.0 = 6.0, and 6.4%600.0 = 6.4.