Can MySQL's CASE Statement Separate Transaction Amounts into Income and Expense Columns?

Populating Columns Conditionally in MySQL Using CASE Statement

Question:

Given a database table containing transaction details, is it possible to generate two columns, "Income Amt" and "Expense Amt," separating the transaction amounts based on whether they represent income or expense using a SQL query?

Solution:

Yes, using the CASE statement in MySQL, it is possible to populate the columns conditionally, separating the amounts based on the "action_type" filed.

Here's a sample query to achieve 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;

This query uses the CASE statement to evaluate the "action_type" column. If the "action_type" is 'Income', it assigns the value of "action_amount" to "income_amt" and sets "expense_amt" to NULL. Conversely, if the "action_type" is 'Expense', it assigns the value of "action_amount" to "expense_amt" and sets "income_amt" to NULL.

As stated by the respondent, MySQL also provides the IF() function as an alternative to the CASE statement. However, the CASE statement is preferred due to its portability across different database engines compared to the MySQL-specific IF() function.

The above is the detailed content of Can MySQL's CASE Statement Separate Transaction Amounts into Income and Expense Columns?. For more information, please follow other related articles on the PHP Chinese website!