Case statement in MySQL

Question

I have a database table called  tbl transaction  with the following definition   id INT 11  Primary Key action type ENUM  Expense    Income   action heading VARCHAR  255  action amount FLOAT   I would like to generate two columns  Income Amt and Expense Amt   Is it possible to populate the columns conditionally  using only a SQL Query  such that the output appears in the correct column  depending on whether it is an Expense item or an Income item   For example   ID        Heading         Income Amt       Expense Amt 1         ABC             1000             - 2         XYZ             -                2000   I m using MySQL as the database  I m trying to use the CASE statement to accomplish this    Cheers

User · Answer

This should work    select    id    action heading    case when action type  Income  then action amount else 0 end    case when action type  Expense  then expense amount else 0 end from tbl transaction

User · Answer

Yes  something like this   SELECT     id      action heading      CASE         WHEN action type    Income  THEN action amount         ELSE NULL     END AS income amt      CASE         WHEN action type    Expense  THEN action amount         ELSE NULL     END AS expense amt  FROM tbl transaction      As other answers have pointed out  MySQL also has the IF   function to do this using less verbose syntax   I generally try to avoid this because it is a MySQL-specific extension to SQL that isn t generally supported elsewhere   CASE is standard SQL and is much more portable across different database engines  and I prefer to write portable queries as much as possible  only using engine-specific extensions when the portable alternative is considerably slower or less convenient

User · Answer

I hope this would provide you with the right solution     Syntax        CASE           WHEN search condition THEN statement list           WHEN search condition THEN statement list              ELSE statement list       END CASE   Implementation   select id  action heading       case when              action type  Expense  then action amount                else NULL                 end as Expense amt         case when                action type   Income  then action amount                else NULL                end as Income amt     from tbl transaction    Here I am using CASE statement as it is more flexible than if-then-else  It allows more than one branch  And CASE statement is standard SQL and works in most databases

User · Answer

MySQL also has IF     SELECT    id  action heading         IF action type  Income  action amount 0  income         IF action type  Expense   action amount  0  expense FROM tbl transaction

User · Answer

Another thing to keep in mind is there are two different CASEs with MySQL  one like what  cdhowie and others describe here  and documented here  http   dev mysql com doc refman 5 7 en control-flow-functions html operator case  and something which is called a CASE  but has completely different syntax and completely different function  documented here  https   dev mysql com doc refman 5 0 en case html  Invariably  I first use one when I want the other

User · Answer

Try to use IF condition  value1  value2   SELECT ID  HEADING   IF action type  Income  action amount 0  as Income  IF action type  Expense  action amount 0  as Expense

[mysql] Case statement in MySQL

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to case