如果另一个字段等于5或10，则不要乘以字段

Question

I'm running this query which is populating my temp table which I then export from. The priceSingle is calculated by simply doubling the fare. However, for some rows, the category is a single fare anyway, so I don't want to double it. The query is actually embedded in a string in PHP, but the SQL looks like:

INSERT INTO temp
    SELECT cruises.code AS sailingId, live, 'USD', 'optioncode', 'rateOptionName', '2',
           category AS cabinCategory, fare*2 AS priceSingle, fare AS priceDouble,
           supplements_usa.adult AS priceAdditional
      FROM fares_usa, supplements_usa, cruises
     WHERE fares_usa.cruise_id = supplements_usa.cruise_id
       AND cruises.id = fares_usa.cruise_id AND cruises.live = 'Y'

How can I avoid doing fare*2 when category equals 5 or 10?

Answer 1

CASE WHEN category = 5 OR category = 10 THEN fare ELSE fare * 2 END