How to Efficiently Sum Points by Budget ID and Transaction Type Using SQL?

Efficient SQL Query for Summarizing Transaction Data

Extracting data based on multiple criteria is a common task in database management. Imagine a "transactions" table containing account_id, budget_id, points, and transaction type (allocation or issue). The challenge is to efficiently calculate the total points for each budget_id, separated by allocation and issue types.

A concise SQL query can elegantly solve this problem:

SELECT budget_id,
       SUM(CASE WHEN type = 'allocation' THEN points ELSE 0 END) AS allocated,
       SUM(CASE WHEN type = 'issue' THEN points ELSE 0 END) AS issued
FROM transactions
GROUP BY budget_id;

This query leverages the CASE statement within the SUM() function. The CASE WHEN type = 'allocation' THEN points ELSE 0 END expression checks the transaction type. If it's 'allocation', the corresponding points value is summed; otherwise, 0 is added. This conditional summation is repeated for 'issue' type transactions.

Grouping the results by budget_id provides a summary showing the allocated and issued points for each budget. The output clearly presents a consolidated view of the transaction data, facilitating analysis and reporting.

The above is the detailed content of How to Efficiently Sum Points by Budget ID and Transaction Type Using SQL?. For more information, please follow other related articles on the PHP Chinese website!