Case statement in MySQL

MysqlSqlDatabaseCase

Mysql Problem Overview


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!

Mysql Solutions


Solution 1 - Mysql

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.

Solution 2 - Mysql

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

Solution 3 - Mysql

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

Solution 4 - Mysql

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

Solution 5 - Mysql

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.

Solution 6 - Mysql

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionPradip KharbujaView Question on Stackoverflow
Solution 1 - MysqlcdhowieView Answer on Stackoverflow
Solution 2 - MysqlBart FriederichsView Answer on Stackoverflow
Solution 3 - MysqlRihoView Answer on Stackoverflow
Solution 4 - MysqlDumitrescu BogdanView Answer on Stackoverflow
Solution 5 - MysqlnomadkbroView Answer on Stackoverflow
Solution 6 - MysqlSushmita KonarView Answer on Stackoverflow