SQL Math: Whole Numbers, Real Numbers

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.

Further reading in BOL:

Using decimal, float, and real Data

Using integer Data

Data Type Precedence (Transact-SQL)

Constants

Let me know if you have any other stumbling blocks when it comes to SQL and math. If I don’t know the answer, I can find out who does!

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

3 thoughts on “SQL Math: Whole Numbers, Real Numbers

  1. Pingback: SQL Awesomesauce » Blog Archive » Learn T-SQL in 5 Minutes

  2. Garrett Strnad

    I come here searching for SQL Math: Whole Numbers, Real Numbers > The MidnightDBA Star-Times.
    Now, Mathematics comes from many different varieties of problems.
    Initially these were within commerce, land dimension, structures and later astronomy;
    today, all sciences suggest problems analyzed by mathematicians, and many problems happen within mathematics itself.
    For instance, the physicist Richard Feynman created the path important formulation of quantum technicians utilizing a combo of mathematical reasoning and physical understanding, and today’s string theory, a still-developing clinical theory
    which makes an attempt to unify the four important
    forces of dynamics, continues to motivate new mathematics.

    Many mathematical items, such as packages of quantities
    and functions, show internal structure because of procedures or relationships that are described
    on the place. Mathematics then studies properties
    of these sets that may be expressed in conditions of that composition; for
    instance quantity theory studies properties of
    the group of integers that may be expressed in conditions of arithmetic procedures.
    In addition, it frequently happens that different such organized sets (or
    buildings) display similar properties, rendering it possible, by an additional step of abstraction,
    to convey axioms for a course of constructions, and then analyze at once the complete class of constructions gratifying these axioms.

    Thus you can study communities, rings, domains and other abstract
    systems; jointly such studies (for constructions identified by algebraic businesses) constitute the website of abstract algebra.

    Here: http://math-problem-solver.com To be able to clarify the foundations of mathematics, the areas of mathematical logic and collection theory were developed.
    Mathematical logic includes the mathematical review of logic and the applications of formal logic to the
    areas of mathematics; placed theory is the branch of mathematics that studies packages or selections
    of things. Category theory, which bargains within an abstract way with mathematical constructions and human relationships between them,
    continues to be in development.

Comments are closed.