Multiplying SUM value from one table with a variable from another

Question

I have two tables:

• t_shipment, which contains shipper_account, ship_value, and ship_date (among others, which are irrelevant for this

• t_customer, which contains account_number and ship_fee (ship fee is the percentage charged to each shipment, and the number varies by account) (likewise, the table contains other fields as well)

  • Note: shipper_account refers to account_number exactly (though it's not treated as foreign key)

I need to calculate the sum for all the transactions done by an account in a day from t_shipment, then multiply it by their corresponding ship_fee in t_customer.

Then I have tried this:

SELECT SUM(ship_value * (ship_fee)) as calculated_value
FROM t_shipment
INNER JOIN t_customer
ON shipper_account = account_number
WHERE ship_fee IS NOT NULL;

But it doesn't look right. I want to make sure that the SUM(cod_value * (cod_fee/100)) part indeed returns the sum of each shipper's ship_value multiplied by their own ship_fee. (e.g., rather than it being the SUM of all ship_value from all shippers multiplied by the SUM of all the ship_fee.

e.g. if I had sets of data:

__________________________________________
|shipper_account | ship_value | ship_date |
|1000000000      |    240     |(some date)|
|1000000000      |     60     |(some date)|
|2000000000      |    100     |(some date)|
-------------------------------------------
_____________________________
|account_number  | ship_fee |
|1000000000      |    0.5   |
|2000000000      |    0.1   |
-----------------------------

The results should be:

• SUM of shipper account 1000000000 (240 + 60) multiplied by 0.5 = 150
• SUM of shipper account 2000000000 (100) multiplied by 0.1 = 10
• SUM of those (150 + 10 = 160)

rather than

• SUM of all ship_values (360) multiplied by SUM of ship_fee (0.6) = 216

So, what would be the simplest solution?

Answer 1

Hope this will work

SELECT SUM(s.ship_value *c.ship_fee) as calculated_value 
FROM t_customer as c inner join t_shipment as s 
on c.account_number = s.shipper_account 
where c.ship_fee is not null 
group by c.account_number

Or

SELECT SUM(ship_value * (ship_fee)) as calculated_value 
FROM t_shipment INNER JOIN t_customer 
ON shipper_account = account_number 
WHERE ship_fee IS NOT NULL GROUP BY account_number;