IF in SELECT statement - choose output value based on column values

Question

SELECT id  amount FROM report   I need amount to be amount if report type  P  and -amount if report type  N   How do I add this to the above query

User · Answer

Use a case statement   select id      case report type         when  P  then amount         when  N  then -amount     end as amount from      report

User · Answer

You can try this also   SELECT id   IF type  p   IFNULL amount 0   IFNULL amount 0    -1  as amount FROM table

User · Answer

Most simplest way is to use a IF    Yes Mysql allows you to do conditional logic  IF function takes 3 params CONDITION  TRUE OUTCOME  FALSE OUTCOME   So Logic is  if report type    p       amount   amount  else      amount   -1 amount    SQL  SELECT      id  IF report type    P   abs amount   -1 abs amount   as amount FROM  report   You may skip abs   if all no s are  ve only

User · Answer

SELECT id          IF type    P   amount  amount   -1  as amount FROM report   See http   dev mysql com doc refman 5 0 en control-flow-functions html   Additionally  you could handle when the condition is null  In the case of a null amount   SELECT id          IF type    P   IFNULL amount 0   IFNULL amount 0    -1  as amount FROM report   The part IFNULL amount 0  means when amount is not null return amount else return 0

User · Answer

select    id    case      when report type    P       then amount      when report type    N       then -amount      else null    end from table

User · Answer

SELECT CompanyName       CASE WHEN Country IN   USA    Canada   THEN  North America           WHEN Country    Brazil  THEN  South America           ELSE  Europe  END AS Continent FROM Suppliers ORDER BY CompanyName

User · Answer

SELECT id  amount FROM report WHERE type  P   UNION  SELECT id   amount   -1  AS amount FROM report WHERE type    N   ORDER BY id

[mysql] 'IF' in 'SELECT' statement - choose output value based on column values

Examples related to mysql

Examples related to sql

Examples related to database