How the Division Operator Works in SQL

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 best way to practice SQL is our interactive SQL Practice track. It contains over 600 hands-on exercises that conver varios SQL topics, from simple one-table queries, through JOINs, to complex topics like subqueries. It lets you train your SQL skills on real business examples.

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 <expression> / <expression>
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.

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

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:

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:

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:

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.

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:

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.