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!
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.
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 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
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.
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
Source: Stackoverflow.com