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 transaction_amount field.

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;

Figure 1 - Subtract amounts in SQL — Figure 1 – Subtracting shipping from the transaction total

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!