How to Divide in SQL
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!
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?