Case statement in MySQL
MysqlSqlDatabaseCaseMysql 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.