September 13, 2012

Arithmetic Operations - SQL Server Vs MySQL

Arithmetic operations done in SQL Server and MySQL may not give the same result. Integer divisions always result in an integer in SQL Server whereas in MySQL, it results to a decimal. Let’s see this with examples.

SQL Server

Run the following code


The result is 2 and not 2.5 This is because both 5 and 2 are Integers and the end result is also converted to the data type of integer. So the actual value 2.5 becomes 2 when implicitly converted to an integer datatype.


This results to the error Divide by zero error because any number divided by zero is infinity.

SELECT 'a'/10

The above throws an error "Conversion failed when converting the varchar value 'a' to data type int."
Now let’s observe arithmetic operations in MySQL, given the same set of data.


Run the following code


The result is 2.5 Although both 5 and 2 are of integer datatypes, MySQL results to decimal datatype during the division


MySQL returns NULL for the above select statement. It won't give Divide by Zero error

SELECT 'a'/10

The above returns 0 and not an error.

Hope these tips were useful and you should keep them in mind when doing arithmetic calculations in MySQL

Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at and a moderator at His T-sql blog is at



0 Responses to "Arithmetic Operations - SQL Server Vs MySQL"

Copyright © 2009-2016 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions