MySQL query adding a value to a new column

Question

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.

sagi · Accepted Answer · 2016-11-03 14:32:34Z

1

You can do it with CASE EXPRESSION :

select milegae.dc_id, 
    mileage.store_id, 
    mileage.mileage, 
    CASE WHEN mileage.mileage > 150 
         THEN ROUND(((mileage.mileage+250) * .75 + 200),2) 
         ELSE ROUND((mileage.mileage * .75 + 200),2) 
    END as TripCost
from mileage;

answered Nov 3, 2016 at 14:32

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ullas Over a year ago

dude, I think 250 should add after all calculations.

sagi Over a year ago

Maybe, not clear at all... The OP can do some of the work too :) @Ullas

Ullas · Accepted Answer · 2016-11-03 14:37:59Z

0

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;

edited Nov 3, 2016 at 14:37

answered Nov 3, 2016 at 14:30

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

1 Comment

El Diablo Over a year ago

Thank you!!! I tried that but my Syntax was incorrect. Your solution works perfectly!!!

Collectives™ on Stack Overflow

MySQL query adding a value to a new column

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related