How to Subtract in SQL
Ready to learn how to subtract in SQL? The great news is that it’s just as easy as performing other mathematical operations in SQL. It might be the easiest operation altogether.
Basic Subtraction in SQL
In our articles on how to add in SQL and how to multiply in SQL, we used our
sales_records table to keep track of the number of items ordered, the price per unit, the sales tax, and the total amount. When we calculated the total amount, we added $5.99 to the total to account for shipping and handling. We did not store this information in a separate field.
So what if we want to see the total transaction amount without shipping and handling? That’s easy. We will subtract $5.99 from the
SELECT id, transaction_date, transaction_amount, customer_name, quantity, unit_price, sales_tax, transaction_amount - 5.99 AS transaction_amount_less_shipping FROM sales_records;
As we can see here, we simply use the subtraction operator to subtract one value from another. As with other math operators, these values can be fields, variables, or literal values.
Let’s say that we want to subtract the shipping amount of $5.99 and also subtract the sales tax amount. That will essentially tell us how much the base unit cost is. (We can get this same value by multiplying
unit_price * quantity, but for this example, we’ll use subtraction.)
SELECT id, transaction_date, transaction_amount, customer_name, quantity, unit_price, sales_tax, transaction_amount - 5.99 - sales_tax AS quantity_price FROM sales_records;
Data Types & Subtraction in SQL
Just as with the other mathematical operators, subtraction in SQL only applies to numeric data types like
int, bigint, money, and decimal. Depending on the database platform you’re using, you will get different error messages or results when you attempt this.
SELECT customer_name - 1 FROM sales_records;
- MS SQL Server will give you an error that says “Conversion failed when converting nvarchar value … to data type int”
- PostgreSQL will give you an error that says “Operator does not exist: character varying – integer”
- MySQL will actually give you a result with a value of “-1”. MySQL essentially treats the character value as a zero (0) and then subtracts from there. Be careful in these cases about getting the result you need!
Also remember that, like addition and multiplication, subtracting with a
NULL value in SQL is going to return
NULL regardless of which side of the operator the
NULL value is on.
SELECT NULL - 1; SELECT 1 - NULL;
And that’s it! That’s how to subtract in SQL. It is one of the simplest mathematical functions you can perform in your database code. 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?