Mysql; if-else condition in Query and result added to a new variable

Question

I have two tables i.e Vendors and Products

Vendors:
id
charges_by (enum 'Order','Product')
extra_fee_per_product
fee_per_order

Prodcuts:
cost
msrp
fee_per_product
vendor_id

I want to see all the products whose margin is less than 100 and greater than 50
Formulae to calculate margin in my project is:

If(Vendors.charges_by=='Order') then 
   Vendor Fees = Products.Cost + Vendors.fee_per_order +
   Vendors.extra_fee_per_product
ElSE IF(Vendors.charges_by=='Product') then Vendor Fees = Products.Cost + 
   Vendors.fee_per_product + Vendors.extra_fee_per_product

Gross Profit = Products.msrp - Vendor Fees

Now we calculate Gross Profit =

Margin = (Gross Profit / MSRP) * 100

I have implemented search in a list and i want to see these records in one query of MySQL

Answer 1

Here you go:

SELECT prod_id, (((msrp + 0.0) - (
  CASE
    WHEN v.charges_by = 'Order'
    THEN p.cost + v.fee_per_order + v.extra_fee_per_product
    WHEN v.charges_by = 'Product' 
    THEN p.cost + p.fee_per_product + v.extra_fee_per_product
  END
)) / msrp) * 100 AS Margin
FROM Vendors v
INNER JOIN Products p
ON v.id = p.vendor_id
HAVING Margin BETWEEN 10 AND 100

Working Fiddle: http://sqlfiddle.com/#!2/ddbaf2/8

The critical part here is msrp + 0.0, it has been done to treat the calculation as a floating point value instead of a number.