Using Case to subtract price in Mysql

Question

I have 3 tables: training_schedules, training_discounts and agents. training_schedules: id, name, agent_id, price. training_discounts: id, agent_id, schedule_id, discount. Agent: ID, name

I tried subtracting the price from the training_schedules table using the discounts column in the training_discounts table, like this:

SELECT ts.id, name, training_types, DATE_FORMAT(date_start ,'%d/%m/%Y') as date_start, 
                DATE_FORMAT(date_end ,'%d/%m/%Y') as date_end, quota, price, td.discount,
                CASE price WHEN td.agent_id = 2  THEN (price - td.discount) ELSE price END as total   
                FROM training_schedules ts
                LEFT JOIN training_discounts td on ts.id = td.schedule_id GROUP BY td.schedule_id;

But no, even if the agent_id is the same, the price of the total column is still the same as before. What could be wrong with my query? If you need it, here's the SQLfiddle: http://sqlfiddle.com/#!9/0cd42d/1/0

Answer 1

You don't need to use group by because you are not using any aggregate function.

SELECT ts.id
  , name
  , training_types
  , DATE_FORMAT(date_start ,'%d/%m/%Y') as date_start
  , DATE_FORMAT(date_end ,'%d/%m/%Y') as date_end
  , quota, price
  , td.discount  
  , CASE WHEN td.agent_id = 2 THEN price - td.discount ELSE price END as total   
FROM training_schedules ts
LEFT JOIN training_discounts td on ts.id = td.schedule_id;

You also used select case incorrectly. Another option is to use the mysql if() function.

if(agent_id = 2, price - td.discount, price) as total