How to Divide in SQL

Are you ready to learn how to divide in SQL? We’ve already covered how to multiply in SQL and add in SQL. Now it’s time to expand our skillset.

Performing division in SQL is just as easy, but there are some things you need to know that you won’t see with multiplication.

Dividing in SQL

We’re going to use our sales_records table to perform our division operations as well. The forward slash is the division operator in SQL, just as it is in most other programming languages. We want to make sure we have the transaction_amount field populated with the total. We use a query to update the total order amount to include the total unit price, sales tax, and $5.99 in shipping.

UPDATE sales_records SET
    transaction_amount = (quantity * unit_price) + sales_tax + 5.99;

Again, note that PostgreSQL may give you an error that the multiplication operator can’t be applied to fields of differing types. It may say: “Operator does not exist: money + numeric”. This is because it sees 5.99 as a numeric field. We can force it to see 5.99 as a money field by typecasting. The query in PostgreSQL is below. Notice the ::money operator at the end of our shipping amount.

UPDATE sales_records SET
    transaction_amount = (quantity * unit_price) + sales_tax + 5.99::money;

Now, let’s say that I want to know the final cost per unit, including tax and shipping. I can run a simple query to divide the order_total by the quantity.

SELECT id, 
       customer_name, 
       quantity, 
       unit_price, 
       sales_tax, 
       transaction_amount, 
       transaction_amount / quantity AS cost_per_unit
FROM sales_records;

When we run this, we’ll see a cost_per_unit column at the end that gives us the calculation we formulated for that field.

Divide in SQL With NULL Values

As with other SQL mathematical operators, dividing with NULL values in SQL will result in a NULL value being returned. Again, because NULL represents “nothing”, anything that you divide by or into NULL is by definition “nothing”.

Let’s try this out. We’ll set one of our records in our sales table to have a NULL transaction_amount and then we’ll re-run the exact same query above.

UPDATE sales_records SET
    transaction_amount = NULL
WHERE id = 2;

We can see here that for the record with an ID of 2, the cost_per_unit comes back as NULL. This is because the transaction_amount is NULL.

It’s important to understand this because if you’re working with calculations, a NULL value can cause unpredictable or unwanted results. In some cases, you may need to update the data to reflect the value you want. It might be that you want your fields to default to zero. Or you may want the value to be NULL because you simply don’t have a value. In any case, make sure that you’re careful about defining the results you want.

Dividing by Zero in SQL

Another issue that arises with dividing in SQL that doesn’t happen with other operators is the “divide by zero” error. Mathematically, you cannot divide by zero and that is the case in SQL as well. If we try, we will get an error in MS SQL that says “Divide by zero error encountered.” (Similar errors are given for PostgreSQL and MySQL.)

SELECT 1 / 0;

Dividing zero by another number will give us a result of zero, which is expected.

SELECT 0 / 5;

Data Types and Division in SQL

Just like we saw with multiplication and addition, if we attempt to divide a non-numeric field then we will get an error. Running this query will produce an error in your database (for PostgreSQL and MS SQL) because the customer_name field is a character field.

SELECT id, customer_name / 2
FROM sales_records;

• MS SQL gives us an error that says: “Conversion failed when converting the nvarchar value ‘Kara Ford’ to data type int.” This is because it attempts to perform the operation on the first record it comes to and fails on that record.
• PostgreSQL tells us “ERROR: operator does not exist: character varying / integer”. This tells us that PostgreSQL does not have a division operator that works when one value is a character value and the other is an integer. (The same would be true if we used a money or float field.)
• MySQL is the unique case here in that it will produce a result, but that result is zero.

If you have a character field in your database that contains numeric values, your database may handle things in different ways. Try updating one of the records in your table to have a numeric value as the customer name.

UPDATE sales_records SET
    customer_name = '1'
WHERE id = 1;

If you run the query to divide the customer_name by 2 now, you’ll get the following results in these databases.

SELECT id, customer_name::numeric / 2
FROM sales_records
WHERE id = 1;

• MS SQL will implicitly typecast the field to a numeric field. It will return a value of 0 because it sees both values as integers and therefore it returns an integer result. It rounds the value down.
• MySQL will return a value of 0.5 because the result of the operation is a decimal value.
• PostgreSQL will give you an error that says “Operator does not exist: character varying / integer”. If you cast your customer_name field (customer_name::numeric) then it will return 0.5 as the result. If you cast it as an integer then it will return 0.

Even if both fields are numeric, you should confirm how your database is handling them as you write your queries. If you’re dividing two integers, do you want the result to include decimal places? If that is the case, you may need to tell your database to treat the numbers as decimals to get the result you need. MySQL will automatically assume you want a decimal result. MS SQL and PostgreSQL will round down.

SELECT 3 / 2;

• MS SQL and PostgreSQL will return 0 because they round down.
• MySQL will return 1.5000.

As you can see, you have to be very careful when performing division operations in SQL. Zeroes, NULL values, and different data types can trip you up easily.

That’s basically it for dividing in SQL! Happy coding!