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!

Answer 1

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.