Search code examples
mysqlsqlsumleft-joininner-join

MySQL - Calculating profit from joining two tables

So I have two tables,

  • one has the product margin before tax, and
  • other table has the individuals products tax % (unfortunately in whole number format).

The code I'm using works, but some products are not taxed, and therefore have 0.

I'm trying to figure out how calculate their profit with the same formula I'm using.

SELECT o.product_id,
       (((o.total_margin/1000)-(p.tax/100)*o.total_margin/1000)/1000) AS Profit
FROM merproduct_offers o
JOIN merchant_products p ON o.product_id = p.product_id
WHERE p.tax > '1'

Linked below are screenshots of the two tables I'm using. Thanks again!

select-statement

Solution

  • You seem to want a left join and coalesce():

    SELECT 
    o.product_id, 
    (((o.total_margin/1000)-(COALESCE(p.tax, 0)/100)*o.total_margin/1000)/1000) AS Profit
FROM merproduct_offers o
LEFT JOIN merchant_products p
    ON o.product_id = p.product_id AND p.tax > 1

    It is likely that the computation can be simplified, but it is hard to tell without an explanation of its purpose. Maybe something like this?

    (o.total_margin / 1000 - COALESCE(p.tax, 0) * o.total_margin / 100000) / 1000 AS Profit