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.

SQL SERVER - Basic Calculation and PEMDAS Order of Operation equ

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)

SQL SERVER - Basic Calculation and PEMDAS Order of Operation equ1

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)

SQL SERVER - Basic Calculation and PEMDAS Order of Operation equ2

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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet
Next Post
SQL SERVER – Not Possible – Delete From Multiple Table – Update Multiple Table in Single Statement

Related Posts

No results found.

35 Comments. Leave new

  • But why did you take the three out of the parentheses though ?

    Reply
  • ANS IS 1

    Reply
  • My nephew asked me this very question, saying that he had said “1”. I said that he was correct. Then he said that a friend of his had said “9”. I wondered how on earth anyone could get 9 from that equation. Looking it over again, I exclaimed, holy sh, it IS 9. I added that it should have been written differently to avoid such errors. Just a pair of parentheses would have done the trick: (6÷2)(2+1). I do acknowledge that the original formulation gives 9, not 1.

    Reply
  • what about treating the equation as a fraction where the numerator is 6 and the denominator is 2(1+2)? Woudn’t that result in 1?

    Reply
  • It depends on whether you use PEMDAS or PEJMDAS. The J in the acronym stand for JUXTIPOSITION. eg. 2(1+2). Juxtaposition happens before multiplication or division. Without the juxtaposition the expression would be 2*(1+2). If you use PEMDAS, the expression 6÷2(1+2) is equal to 9. If you use PEJMDAS, the expression 6÷2(1+2) is equal to 1.

    Curiously, I asked ChatGPT which was correct, PEMDAS or PEJMDAS. It’s answer was PEMDAS. Then I asked it to solve the equation 6÷2(1+2). The answer given was 1.

    It used PEJMDAS to solve the problem after saying it was not the correct method to solve the problem!

    Reply
  • I’ve run into a problem with SQL Server, on this very topic, that I just cannot untangle.

    I am converting/assembling separate geo coordinate parts into a single decimal degree value.

    It is straightforward (IMHO), and I’ve done it dozens of times, successfully, in various contexts (Excel formulas or SQL Queries etc.)
    Recently, I tried this in SQL Server, and it simply returns the wrong answer.

    Seems to me it should be calculated as follows:
    (Here – we’ll just use latitude for example, and let “Dir” refer to the hemisphere (N or S), and using N as positive latitude:

    Lat_Dec_Deg = iif(Lat_Dir = ‘S’, -1, 1) * (Lat_Degrees + Lat_Minutes/60 + Lat_Seconds/3600)

    (You’ll have to give me a little latitude (no pun intended) on the use of “iif” — you get the gist. In SQL Server it seems one must instead use the ‘case’ command:

    (case lat_direction when ‘S’ then -1 else 1 end) * (lat_degrees + lat_minutes/60 + lat_seconds/3600)

    I don’t see any room for false interpretation in this. The division operations MUST happen before the additions, and I’m thinking that anything to do with this ‘unary’ negative 1 thing should be handled with the parentheses.

    As far as I can tell, SQL Server does NOT reliably generate the right answer with this formula. And I can’t even figure out what it IS doing. (Example: 39 deg, 56 min, 29.3 sec North: In SQL Server it gives 39.008138. This is clearly wrong. 56 minutes puts it darn near 40 degrees. According to my calculations it should be about 39.9415.)

    Am I missing the forest for the trees here? Or does anyone else agree that the above should work as written?

    Reply

Leave a Reply