Back to articles list June 4, 2021 - 5 minutes read How the Division Operator Works in SQL Andrew Bone Andrew is a technical writer from Dublin, Ireland. He started his career as a full-stack software developer. However, over time, he found himself enjoying writing documentation and user guides more than programming. He is now focused on breaking down complex subjects so that the writing is clear and understandable, regardless of the readers’ technical background. Tags: sql learn sql division The division operator in SQL is used to divide one expression or number by another. This article will show you exactly how to use it and common mistakes to avoid along the way. The division operator in SQL is considered an arithmetic operator. The arithmetic operators are addition (+), subtraction (-), multiplication (*), division (/), and modulus (%). This article will focus on the division operator, discussing the rules that must be followed along with some common mistakes to look out for when trying to divide in SQL. The syntax for the division operator in SQL is as follows: SELECT / FROM table [WHERE expression] Note the inclusion of the WHERE clause is entirely optional. The division operator can be used anywhere there is an expression. This means you can use the SQL division operator with: SELECT. WHERE. HAVING. ORDER BY. Or as part of a function. If these expressions cause you any confusion, consider this SQL Practice track from LearnSQL.com. It is an efficient and fun method for sharpening your SQL skills. The simplest example of the division operator is: SELECT 10 / 2 You can execute this query, and it will output the result – in this case, 5. However, it is more likely that you will be working with integers that reside in columns as part of your database tables. Let’s look at such an example. We will use a table called stock, containing typical food items along with their price and quantity. stock itempricequantity Eggs1512 Milk34 Flour52 Bread103 Let’s apply the division operator to an entire column of our table and witness its effect. Consider the following SQL query: SELECT quantity, quantity / 2 AS 'result' FROM stock_level With this query, we are selecting the quantity column then showing the result of dividing the quantity value in our result column which contains the result of quantity / 2. Let’s run this query and observe the results: quantityresult 126 42 21 31 The results look like what we would expect, except for the final row. Isn’t 3 / 2 = 1.5?! The result shown by SQL Server shows a value of 1. Depending on the variant of SQL you are using, the division operator may handle the integer division differently. Let’s clear this up in the next section! How Are Integers Divided in SQL? The integer divisions may behave differently depending on your choice of SQL database management system. MySQL and Oracle users can expect to see the real number to be shown with the data type being float, e.g., 3 / 2 = 1.5. However, for SQL Server and PostgreSQL users, the integer division is more complex. You will have to remember certain rules when dividing numbers that do not divide to an integer. Let’s bring up that previous example again: SELECT 3 / 2; When executed in SQL Server or PostgreSQL, the result of this query is 1, while most users would expect 1.5. What is happening here? The division operator only handles the integer part of the result when dividing two integers. The result obtained is called the quotient. The remainder is not dealt with. With the division of two integers, the result will be how many times one number will go into another, evenly. Consider these examples in SQL Server or PostgreSQL: Division QueryResult SELECT 10 / 33 SELECT 5 / 22 SELECT 11 / 61 Change the Operands to a Decimal or Floating-Point Number This can only be done if the particular division operation allows it. If two column names are involved, this method will not be possible; instead, use the CAST/CONVERT method described below. You need to be able to access one of the numbers directly. Using this method is simple; we simply change one or both of the operands: Division QueryResult SELECT 10.0 / 33.333333 SELECT 5 / 2.02.5 SELECT 11.0 / 6.01.833333 We can also apply this method to our previous stock example. We have to update our query to the following: SELECT quantity, quantity / 2.0 AS 'result' FROM stock_level Executing this query produces a similar result set as before with one key difference. quantityresult 126.00000 42.00000 21.00000 31.50000 This is ideal if we can access and alter one of the operands directly. However, this is not always the case. Using CAST or CONVERT on Columns Imagine we divide the value of the price column by the quantity column for a particular item. Our query would look like this: SELECT price / quantity FROM stock WHERE item = 'Flour' Executing this query shows a result of 2; this is a classic integer division scenario. Let’s get around this by using CAST on one of our columns. We have to update our query to the following: SELECT CAST(price AS DECIMAL(7,2)) / quantity AS 'CAST(price)/quantity' FROM stock WHERE item = 'Flour' Note that you could also use CONVERT instead of CAST. You can read more about converting an integer using CAST and CONVERT here. Executing this query shows a new result: CAST(price)/quantity 2.50000 There we have it! The CAST operation was successfully applied to the price column, making it a decimal data type for this arithmetic operation. Note that if the arithmetic expression contains more than one operator, the multiplication and division operators are evaluated first, followed by the addition and subtraction operators. When two operators have the same priority, the expression is evaluated from left to right. You should also be aware of the SQL “division by zero” error regardless of what DBMS you use. Dividing by zero results in infinity, which SQL does not allow. Doing so will result in an error message being shown. This useful SQL cookbook page shows you how to handle the “division by zero” error in SQL. Check it out if you’re interested! Divide and Conquer With the SQL Division Operator! In this article, we dove into the deep end, showing you the intricacies of using the division operator in SQL for the integer division. We learned how to handle integer division for each SQL DBMS from SQL Server to MySQL. We also touched on the pesky SQL “division by zero” error and introduced resources that will allow you to handle this particular error elegantly. As with anything in life, practice makes perfect! If you would like to strengthen your SQL skills through fun and engaging monthly challenges, consider this month’s SQL Challenge. Tags: sql learn sql division You may also like How to Convert an Integer to a Decimal in SQL Server Read more How to Handle Divide by Zero In SQL Read more How to Use Comparison Operators with NULLs in SQL The SQL NULL value serves a special purpose, but using it is tricky. In this article, we teach you all the tricks of using NULLs with comparison operators. Read more What Do the Operators LIKE and NOT LIKE Do? Learn all about the operators LIKE and NOT LIKE in SQL! Level up your queries with these commands to extract the exact data you need. Read more Using AND, OR, and NOT Operators in SQL Want to learn more about AND, OR, and NOT operators in SQL? This article teaches you to use these operators and bring your WHERE clauses to the next level! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.