How Can MySQL CASE Statements Populate Columns Conditionally?

Using Case Statements for Conditional Column Population in MySQL

With the requirement to conditionally populate two columns ('Income Amt' and 'Expense Amt') in the 'tbl_transaction' table, a case statement proves to be an effective solution in MySQL.

The case statement allows you to evaluate an expression and return a different value based on whether a condition is met or not. In this case, the action_type column is used as the condition to determine whether an amount should be assigned to the Income Amt or Expense Amt column.

Here's a query that demonstrates the use of a case statement to achieve the desired result:

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;

In this query, the CASE statement is used twice, once to assign the action_amount to the income_amt column if the action_type is 'Income', and once to assign the action_amount to the expense_amt column if the action_type is 'Expense'.

The output of this query would be as follows:

id	action_heading	income_amt	expense_amt
1	ABC	1000	NULL
2	XYZ	NULL	2000

This demonstrates how a case statement can dynamically populate columns based on conditional expressions, providing a convenient way to categorize and organize data effectively.

The above is the detailed content of How Can MySQL CASE Statements Populate Columns Conditionally?. For more information, please follow other related articles on the PHP Chinese website!