user3197661 user3197661 - 1 year ago 49
MySQL Question

How to calculate from 2 tables in SQL

How to create query for join Table A and B with condition A.salary * B.rate

Table A
customerID salary
A1 100
A2 200
A3 300

Table B
customerID rate
A1 2
A2 3
A3 4

My expect result.

customerID salary
A1 200
A2 600
A3 1200

Answer Source
SELECT t1.customerID,
       COALESCE(t1.salary * t2.rate, 'NA') AS salary
FROM TableA t1
    ON t1.customerID = t2.customerID

I used COALESCE when computing the effective salaries in case either the salary amount or rate be NULL for any customer.

Demo here: