Create new column which value is the subtraction of 2 columns on different tables

user_1757383 0
Viewed 1265

enter image description hereI have done a table named invoice and on this table I would like to create a column PROFIT. To obtain the value of PROFIT I need to subtract the column amountUsd from table invoice MINUS the value amountUsd on table carriersPayments. Both tables have the exactly same column loadNumber.

mysql
1 Answers

You can use the following SELECT:

SELECT i.*, i.amountUsd - cp.amountUsd AS PROFIT 
FROM invoice i INNER JOIN carriersPayments cp ON i.loadNumber = cp.loadNumber

To substract multiple carriers payments you can use the following query:

SELECT i.id, i.loadNumber, i.amountUsd - SUM(cp.amountUsd) AS PROFIT 
FROM invoice i INNER JOIN carriersPayments cp ON i.loadNumber = cp.loadNumber
GROUP BY i.id, i.loadNumber, i.amountUsd

demo on dbfiddle.uk

user_38408400
Related