Substract the price using Case in Mysql-CodePudding

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. agents: id, name

I try to subtract the price from training_schedules table with the discount column in 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 it doesn't right, the total column is still the same price as before even if agent_id is the same. What can possibly be wrong with my query? Here's the SQLfiddle if needed: http://sqlfiddle.com/#!9/0cd42d/1/0

CodePudding user response：

You don't need to use group by since you are not using any aggregation functions.

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 are also using the select case wrongly. Another option is to use mysql if() function.

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