CODEWITHSUNDEEP
mysql
user1757383
user1757383

Reputation: 91

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

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.

Upvotes: 3

Views: 1889

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43594

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

Upvotes: 4

Related Questions