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!

@Pradip: "I'm trying to use CASE statement." Where is that ? Post that too. — Hardik Mishra, Mar 07 '13 at 07:56
SELECT *, CASE WHEN action_type = 'Expense' THEN 'expense_amount' ELSE 'income_amount' END FROM tbl_extra_income_expense — Pradip Kharbuja, Mar 07 '13 at 07:57
I'm just getting expense_amount or income_amount string not value. — Pradip Kharbuja, Mar 07 '13 at 07:57
Take out the quotes and you'll get values instead of strings. — Barmar, Mar 07 '13 at 08:03

cdhowie · Accepted Answer · 2014-09-03T23:47:56.167

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.

score 23 · Answer 2 · answered Mar 07 '13 at 07:59

23

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

answered Mar 07 '13 at 07:59

Bart Friederichs

33,050
15
95
195

score 10 · Answer 3 · answered Mar 07 '13 at 07:56

10

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

answered Mar 07 '13 at 07:56

Riho

4,523
3
33
48

score 4 · Answer 4 · answered Mar 07 '13 at 07:57

4

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

answered Mar 07 '13 at 07:57

Dumitrescu Bogdan

7,127
2
23
31

score 2 · Answer 5 · answered Aug 03 '15 at 17:52

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.

score 2 · Answer 6 · edited Nov 18 '17 at 02:26

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.

Case statement in MySQL

6 Answers6

Linked

Related