El Diablo El Diablo - 1 month ago 17
MySQL Question

MySQL query adding a value to a new column

Trying to do a query but need to add 250 to TotalCost if mileage is > 150.

select milegae.dc_id,
mileage.store_id,
mileage.mileage,
round((mileage.mileage * .75 + 200),2) as TripCost
from mileage;


This query gives me the data I want but I'm unsure how to add 250 to the TotalCost given the parameters.

Answer

Use a CASE expression to check whether the mileage > 150.

Hope you want to add 250 to the final value if the mileage value is greater than 150.

Query

SELECT 
  milegae.dc_id, 
  mileage.store_id, 
  mileage.mileage,
  CASE WHEN mileage.mileage > 150 
    THEN round((mileage.mileage * .75 + 200),2) + 250
    ELSE round((mileage.mileage * .75 + 200),2) 
  END AS TripCost
FROM mileage;