SQL SERVER – Basic Calculation and PEMDAS Order of Operation

After thinking a long time, I have decided to write about this blog post. I had no plan to create a blog post about this subject but the amount of conversation this one has created on my Facebook page, I decided to bring up a few of the question and concerns discussed on the Facebook page. There are more than 10,000 comments here so far.

equ SQL SERVER   Basic Calculation and PEMDAS Order of Operation

There are lots of discussion about what should be the answer. Well, as far as I can tell there is a big debate going on on Facebook, for educational purpose you should go ahead and read some of the comments. They are very interesting and for sure teach some new stuff. Even though some of the comments are clearly wrong they have made some good points and I believe it for sure develops some logic. Here is my take on this subject. I believe the answer is 9 as I follow PEMDAS  Order of Operation. PEMDAS stands for  parentheses, exponents, multiplication, division, addition, subtraction. PEMDAS is commonly known as BODMAS in India. BODMAS stands for Brackets, Orders (ie Powers and Square Roots, etc), Division, Multiplication,  Addition and Subtraction. PEMDAS and BODMAS are almost same and both of them follow the operation order from LEFT to RIGHT.

Let us try to simplify above statement using the PEMDAS or BODMAS (whatever you prefer to call).

Step 1: 6 ÷ 2 (1+2) (parentheses first)
Step 2: = 6 ÷ 2 * (1+2) (adding multiplication sign for further clarification)
Step 3: = 6 ÷ 2* (3) (single digit in parentheses – simplify using operator)
Step 4: = 6 ÷ 2 * 3 (Remember next Operation should be LEFT to RIGHT)
Step 5: = 3 * 3 (because 6 ÷ 2 = 3; remember LEFT to RIGHT)
Step 6: = 9 (final answer)

Some often find Step 4 confusing and often ended up multiplying 2 and 3 resulting Step 5 to be 6 ÷ 6, this is incorrect because in this case we did not follow the order of LEFT to RIGHT. When we do not follow the order of operation from LEFT to RIGHT we end up with the answer 1 which is incorrect.

Let us see what SQL Server returns as a result.

I executed following statement in SQL Server Management Studio

SELECT 6/2*(1+2)

equ1 SQL SERVER   Basic Calculation and PEMDAS Order of Operation

It is clear that SQL Server also thinks that the answer should be 9.

Let us go ahead and ask Google what will be the answer of above question in Google I have searched for the following term: 6/2(1+2)

equ2 SQL SERVER   Basic Calculation and PEMDAS Order of Operation

The result also says the answer should be 9.

If you want a further reference here is a great video which describes why the answer should be 9 and not 1.

And here is a fantastic conversation on Google Groups.

Well, now what is your take on this subject? You are welcome to share constructive feedback and your answer may be different from my answer.

NOTE: A healthy conversation about this subject is indeed encouraged but if there is a single bad word or comment is flaming it will be deleted without any notification (it does not matter how valuable information it contains).

Reference: Pinal Dave (http://blog.SQLAuthority.com)

22 thoughts on “SQL SERVER – Basic Calculation and PEMDAS Order of Operation

  1. Hi Pinal,

    This is kiran i am new to SQL server and i need to list out all the tables used in stored procedure. collecting manually is very tedious job for such a long procedures so i am searching for alternative smart way. please suggest me how could i.

    Thanks
    Kiran

    Like

  2. You just got trolled hard, so hard that it consumed a lengthy blog post and much distress. Roughly 98% of the people that debate that are clearly trolling for the sake of rustling some jimmies. Congrats Pinal, you may have been their biggest catch with that bait.

    Like

  3. Occam’s Razor applies here:
    “Never assume malice when stupidity is equally applicable”

    It is fairly basic stuff, but I’m not surprised a lot of people don’t remember it.

    Like

  4. I’m slightly confused. PEMDAS: Parentheses [(2+1)], exponents [none], multiplication [2*(2+1)] = [2*3] = [6], division [6 / 2 * (2+1)] = [6 / 6], addition [none], subtraction [none]. 6 / 6 = 1.

    Wouldn’t this mean the difference between PEMDAS and BODMAS is the reason for different results? Thus PEMDAS can’t be commonly known as BODMAS in India. Instead, BODMAS is used instead of PEMDAS in India?

    Like

    • And to reply to myself, searched more of this and found out it is actually PE(MD)(AS) (left to right). Didn’t even remember such a thing. For some reason my mind turns 2(2+1) more important than 2 * 3. Anyway, glad to see this conversation as now I don’t need as much parenthesis to make things work for sure when scripting, I just need to remember the left-to-right rule of PEMDAS. :D

      Like

  5. Hi all. I researched this topic well for about 2 weeks now and have come to the following conclusions. I will summarize what I have said in other forums

    with respect to the notations, then I will address other points.

    First,

    if you want to say 0.5x, then you HAVE to write (1/2)x with parentheses or, x “all over 2” with a horiztonal fraction bar, or write x/2. I have never

    seen (1/2)x before I researched this equation, but since searching online, I HAVE seen fractional coefficients written this way, only because computers

    are limited to the horizontal typing space.

    Therefore:

    x/2 = (1/2)x = 0.5x

    1/2n = 1/(2n) This sort of notation is used especially with pi, ln, or e. We have never had to say 1/(2pi). It was simply 1/2pi, or 1/2e^2.

    I have always used ab/cd to mean (ab)/(cd) and I topped almost all of my calculus classes since high school through university.(moot point, I know)

    Just to re-iterate, to use 6/2 as a fraction, parentheses are REQUIRED. Every book will tell you this.

    Now consider the Identity Law:

    a = 1a = 1(a)

    We know there is ALWAYS an ‘invisible’ 1 as a ceofficient of a variable if no other number is there. Therefore:

    a/a = 1, and if a is also 1a, then a/1a = 1. Blindly using ‘pemdas’, some folks would do this:

    a/1a = a/1*a = a*a = a^2. I hope this drives home the silliness of this calculation.

    Now, on to my second point:

    consider: factoring, simplifying equations, and the distributive property.

    Lets start with the number 6.

    6 = (4+2). There is a common factor here: 2. So let’s factor it out of both terms.

    (4+2) = 2(2+1). The outside 2 remains a part of of the 2 inner terms at all times. It cannot be used in an operation by itself without the rest of (4+2).

    The reverse of factoring is distribution, so, 2(2+1) = 6. This has to be true always. The argument I have seen to this is that (6/2) can be distributed.

    This is true ONLY is 6/2 is in parentheses, otherwise, the 6 and 2 are separated by a division slash, and the 2 is a factor of 2+1.

    So, let’s prove the initial equation:

    6/6 = 1

    6/(4+2) = 1

    6/2(2+1) = 1

    the same can be done for other factors:

    6/6 = 1

    6/(3+3) = 1

    6/3(1+1) = 1

    Distribution is actually a part of “Simplifying Equations” and is not bound to the order of operations as “multiplication”, since it is in fact “removing

    parentheses by distributing”. This can be googled and several references found.

    Simplifying 2(2+1) + 3(2+1) = 5(2+1). We “combined like terms” here, by adding, and did not perform the “parntheses” part of order of operations, nor did

    we multiply, which is also higher priority than adding, because we only simplified.

    Lastly, I hear the argument that “This is strictly numbers and you don’t use algebra rules since there are no variables”. That is the most asinine

    arguement I have heard yet. All axioms, laws, and properties use variables, meaning that they hold true for “any number”, hence the proofs with

    variables.

    I welcome thoughts on this, in an intellectually formed response. I am tired of the ‘flaming’ that goes on by imbciles on some other forums with

    rebuttals like “it is 9. go back to grade 3 you moron”, or “google says it is 9”, when google changes the equation to (6/2)*(2+1), and wolfram

    contradicts itself with 2n/2n = 1, and 6/2n = 3/n, but then says 6/2(2+1) is 9. wolframs “terms” state that any answer should be verified with common

    sense and accuracy should also be verified.

    Like

  6. The 2 in front of the parenthesis implies possession, doubling the parenthesis is the intent. Otherwise an alternative symbol would have been used to imply multiplication. In which case 9 would be correct.
    6/2*(1 + 2) = 9
    6/2(1+2)= 1

    Like

    • I totally agree.
      This equation is 6 over 2(2+1).
      No one ever wrote a / bc when they meant ac / b
      That is because they are different expressions.
      Distributive property cannot change the final value,
      and, when used, is 6 / (4+2).
      One more thing, do the factoring for 6 + 3 = 9
      Now factor 6/2 from 6 + 3 and we get:
      (6/2)2 + (6/2)1 = 9
      Notice how 6/2 MUST be in parentheses?
      Now final step:
      (6/2)(2+1)
      The same simple idea applies to “Foiling” binomials:
      x+1(x+2) = x + ((1)x + (1)2)
      (x+1)(x+2) = (x+1)x + (x+1)2

      Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  8. Mathman, i quite follow your explanations and found it interesting. I’m a proponent that 6/2(2+1) is 9.

    Your explanation from the point view of 6/6 overlooked one math rule; Introduction of parenthesis or bracket.

    While 6/(2(2+1)) = 1,

    6/2(+1) = 9.

    What helps our communications in math is the parenthesis or bracket.

    6/6 = 6/(4+2) = 6/(2(2+1))! Here, as a math student one knows that the 2(2+1) is one as a denominator. However,

    6/2(2+1) = 6/2x(2+1), which when either BODMAS or PEMDAS with LEFT TO RIGHT is applied you’ll get 9.

    6/2x(3) = 3×3=9..

    Now consider, 1/2n was shown in your comment.

    This could be interpreted as (1/2) x n or 1 /(2n)

    You’ll agree with me that both would give different answers. But in simple math operation, the expression if written the way it’s stated, i.e. 1/2n, any procedure or system of calculating it would take (1/2) x n. If the other is meant, it must surely be communicated with parenthesis or bracket, i.e. 1/(2n) any no mathematical method or procedure of calculation would mistake its answer.

    Therefore beloved mathman, the expression 6/2(2+1) would simply be solved as 6/2*3=3*3=9.

    Thank you.

    Like

  9. Ifeoluwa,
    “any procedure or system of calculating it”
    And any machine’s solution needs to be verified by a human for correctness. I am not talking about “how a machine would parse this equation”. I am talking about how humans have solved this for the last couple hundred years. Not sure how this will turn out on this forum, but let’s try:

    Intro to Real Analysis by Bartle and Sherbert:
    —————-QUOTE——-
    X :=

    1
    — : n ∈ ℕ
    2n

    or more simply: X = 1/2n
    ———-End Quote———
    You can verify that passage by googling the eBook.

    The introduction to machines and programs have very much clouded the way things have been done historically. Wolfram|Alpha changed its code in 2013 and was solving it as above, before the change.

    One of your arguments relies on : 2(2+1) = 2 x (2+1)
    But doesn’t 2(2+1) also equal (2x(2+1)) ??
    This is why the equation is ambiguous.

    Since you also used machines as an argument, I strongly welcome your comment the following: Use wolfram|alpha and type in:
    First example: cos2a
    Second: cos2a/cos2a
    Third: cos2a/b/cos2a/b

    You can also find examples of algebraic division on a mathematician’s math page at Syracuse University:
    http://cstl.syr.edu/fipse/algebra/unit2/parenth.htm
    Eg: 6 / 2n = 3/n
    Now let n=2+1 and solve both sides…

    Remeber how we were taught to simplify algebraic division?
    1 – Divide the coefficients of the like terms
    2 – Subtraction the powers (exponents) of the like terms

    Eg: 4a^2 / 2a = ??
    Divide coefficients: 4 / 2 = 2
    Subtract powers: 2-1 = 1
    result: 2a

    I stand by 1 as how I would solve it and how my colleagues would accept and understand it, however, after researching the topic, it is very clear how ambiguous it is :)

    I look forward to your comments on these points.
    King Regards,
    your beloved mathman :)

    Like

  10. Hopefully published math books are able to shed some light on this topic.

    According to Measure and Integral: An Introduction to Real Analysis
    By Richard Wheeden, Richard L.

    μ/2(k+1) =

    μ
    ——-
    2(k+1)

    Let μ=6, k=2. What is the result?

    No math text I have ever seen writes: a/bc = (a/b)c. “bc” is a composite quantity. So directly entering such a thing into a computer (today), may result in an incorrect solution, thus using parentheses might be required when using calculator.

    What is a composite quantity?

    Bonnycastle’s introduction to algebra
    By John Bonnycastle, James Ryan, John Francis Jenkins
    …3abc is a composite quantity, the factors of which are 3, a, b and c.

    You can’t divide by the (3) in the above quantity, and then multiply by abc, because you changed the quantity.

    1/2y = (1/2)y is NOT a “standard” by any math text that I have seen.

    An Introduction to Real Analysis: The Commonwealth and International Library
    By Derek G. Bal

    ε/2K =
    ε

    2K

    Let’s suppose ε=6 and K=2+1 here. What is the result ?

    Let’s not forget

    Introduction to Real Analysis
    By Bartle and Sherbert (3rd Edition)

    where 1/n(n+1) =
    1
    ——–
    n(n+1)

    Let’s apply this to, for example, :
    6/n(n+1) and let n=2. What is the result ?

    What notation do they use to denote a fractional coefficient?
    F(x) := (2/3)x^(3/2)

    Mathematical Constants? Same concept:
    π/2n means π/(2n). We don’t need the ( ) to clutter things up. It comes with experience of doing mathematics at this level.

    And many more examples from the books:

    g’ (1/2nπ) 3, then 0 < n² / n! < n/(n – 2)(n – 1) < 1/(n – 3)

    Notice how they don't pull some BS move where:
    n/(n – 2)(n – 1) = n / (n-2) x (n – 1) = n(n – 1) / (n – 2)

    I think everyone gets the point now. What book says 1/n(n+1) = 1 / n x (n+1) = (n+1)/n?
    None. That's how many.
    If no books use this notation, would you consider it STANDARD ?

    What books uses 1/2y = (1/2)y instead of 1 / (2y)?
    None.

    A Radical Approach to Real Analysis By David M. Bressoud:

    Does he write 1/2(200/199) when they mean (1/2)(200/199)?
    NOPE. He writes it as:
    (1/2)(200/199)

    What about (1/2)x ? Just that. Not 1/2x.

    Do you believe authors of calculus books or keyboard mathematicians? That is for you to decide.

    Bonnycastle's introduction to algebra
    By John Bonnycastle, James Ryan, John Francis Jenkins

    Division:
    RULE: Set the dividend OVER the divisor in the MANNER OF A FRACTION, and reduce it to its simplest form, by cancelling letters and figures that are common to each term.

    Examples (from the book)

    6ab ÷ 2a =

    6ab
    —– = 3b
    2a

    and wikipedia that says 1/2y = 1 / (2y) is exception to the "standard", yet I can't find a single text that uses 1/2y = (1/2)y.

    So according to the authors of published university calculus books, if you want 9 to be your answer, then the correct notation is:
    (6/2)(2+1) = 9

    Otherwise, we can deduce from 1/n(n+1), that 6/n(n+1) = 1 ; if n=2.

    Dave Pinal showed all the steps to "pe(md)(as)", However in step 2, he "added a multiplication sign for 'further clarifcation'."

    This of course changes the interpretation, as seen above.
    1 / 2y = 1 / (2y)
    but
    1 / 2 * y = y / 2

    I can also show the steps.
    First. rewrite as a fraction like every math books shows you:
    If 6/2(a+b) =
    6
    —–
    2(a+b)

    then 6/2(2+1) =
    6
    ——–
    2(2+1)

    Step 1: Parentheses
    6
    —-
    2(3)

    Step 2: Division OR Multiplication. I need to add here, that there is NO NECESSITY to go LEFT TO RIGHT. This is by the axioms of the number system. You collapse equations according to precedence.
    I will demonstrate:
    Since mult. and div. are equal precedence, choose either one first. Say, division.
    6 / 2 = 3/1, giving:
    3
    —- then division again, (since you removed the multiplicand by division) resulting in 1.
    3

    Or, multiplication first: 2(3) = 6, giving:
    6
    —- then divide, resulting in 1.
    6

    Everyone running around these days, typing "LEFT to RIGHT" in giant letters is removing the underlying understanding of why left to right "works". I can demonstrate for any equation, that "Left to Right" is not required. That said, what is not allowed is to add parentheses around certain portions of an equation, which of course changes things drastically, which is what the associative property demonstrates.
    Example:
    8 ÷ 4 ÷ 2 = ?

    The operands and their associated operators must remain together. Thus, I can do this operation in any order I want. I can divide by EITHER the 4 or the 2 first, then the other one:
    8 ÷ 4 ÷ 2 = 8 ÷ 2 ÷ 4
    That is because it is really: 8 (÷4) (÷2), which translates to:
    8 * (1/4) * (1/2)

    What you cannot do is: 8 ÷ 4 ÷ 2 = 8 ÷ (4 ÷ 2).
    This is obviously an incorrect math statement since 8 ÷ (4 ÷ 2) = 8 ÷ 4 * 2 ; which is clearly not 8 ÷ 4 ÷ 2

    Again, if you really understand the math basics, you'd know that "left to right" is only a tool for people who don't.

    IMO, the major crux to the OP's argument is: "calculator's inline parsing convention".
    We can't blindly enter things into calculators. Some knowledge of the input is required, as well as some knowledge of the computer's parsing convention. If we know that 1/2y is 1/(2y), but the computer will make it (1/2)y, then we add the necessary ( ). As the saying goes when entering info into a computer: Garbage in = Garbage out.
    The OP showed the steps that the CALCULATOR uses to get 9, which is an explanation of why the calculator gets "9".

    For hundreds of years, equations of the type 6/a(a+b) have been interpreted
    6
    ——–
    a(a+b)

    and is still published in most (if not all) math, analysis, and algebra texts today.
    Let a=2, and b=1.

    Any Questions?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s