How to subtract one value from another with SQL

Problem

You need to subtract one value from another in a SQL statement.

Sample Data

Let's consider a sample table named orders which keeps track of the total cost and the discount applied to each order:

The goal is to subtract the discount from the total_cost for each order.

MySQL Solution

SELECT order_id, total_cost, discount, (total_cost - discount) AS discounted_cost
FROM orders;

PostgreSQL Solution

The solution in PostgreSQL is identical to MySQL:

SELECT order_id, total_cost, discount, (total_cost - discount) AS discounted_cost
FROM orders;

Explanation

In both MySQL and PostgreSQL, we use the - operator to perform subtraction. The SQL query selects all columns from the orders table, and also calculates a new column discounted_cost by subtracting discount from total_cost.

The expression (total_cost - discount) performs the subtraction for each row in the table. The AS discounted_cost part of the statement assigns a name to the new calculated column. The result of this SQL statement will be a table that includes the order_id, total_cost, discount, and the discounted cost for each order.