How Can I Populate Conditional Columns in MySQL Using a CASE Statement?

Populating Conditional Columns Using a Case Statement in MySQL

When working with database tables, it often becomes necessary to create new columns that dynamically populate data based on specific conditions. MySQL provides the CASE statement, which allows users to conditionally evaluate and assign values to columns.

Consider a scenario where a table called 'tbl_transaction' exists with columns for transaction ID, action type (Expense or Income), heading, and amount. The goal is to generate two additional columns: Income Amt and Expense Amt, which should be populated with the appropriate amounts based on the transaction type.

To achieve this using a SQL query, the CASE statement can be employed:

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 evaluates the action_type column for each transaction, and based on its value, assigns the corresponding amount to either the Income Amt or Expense Amt column. When the action is Income, the action_amount is assigned to Income Amt, while for Expense, it is assigned to Expense Amt. For actions that are neither Income nor Expense, NULL is assigned to both columns.

As a result, the query produces an output table with the desired columns, where each row contains the transaction ID, heading, Income Amt (if applicable), and Expense Amt (if applicable). This allows for easy analysis and categorization of transactions based on their types.

The above is the detailed content of How Can I Populate Conditional Columns in MySQL Using a CASE Statement?. For more information, please follow other related articles on the PHP Chinese website!