How to Efficiently Retrieve Multiple Sum Aggregations with Conditional Filtering in MySQL?

MySQL: Efficiently Retrieving Multiple Sum Aggregations with Conditional Filtering

Working with databases often requires retrieving multiple datasets based on varying conditions. This can be particularly complex when dealing with different field values within a single query.

The Challenge:

Consider a transactions table with columns id, account_id, budget_id, points, and type. The goal is to retrieve, in a single query, the sum of points for each budget_id where type equals 'allocation', and separately, the sum of points where type equals 'issue'.

The Solution: Leveraging MySQL's IF() Function

MySQL's IF() function provides a concise solution. We can use it within a SUM() aggregate, combined with GROUP BY, to achieve the desired result:

SELECT 
    budget_id, 
    SUM(IF(type = 'allocation', points, 0)) AS allocated,
    SUM(IF(type = 'issue', points, 0)) AS issued
FROM 
    transactions
GROUP BY 
    budget_id;

Query Breakdown:

• IF(type = 'allocation', points, 0): This conditional expression checks each row. If type is 'allocation', it returns the points value; otherwise, it returns 0.
• IF(type = 'issue', points, 0): This mirrors the above, but for type = 'issue'.
• SUM(...): The SUM() function calculates the total for each conditional expression, effectively summing points separately for 'allocation' and 'issue' types.
• GROUP BY budget_id: This groups the results by budget_id, providing the aggregated sums for each budget.

Illustrative Example:

The query, when executed against the transactions table, might produce a result set like this:

<code>budget_id | allocated | issued
----------|-----------|--------
     434  |   200000  |   100
     242  |   100000  |  5020
     621  |    45000  |  3940</code>

This demonstrates the efficient retrieval of multiple aggregated datasets with distinct conditional filtering within a single, optimized MySQL query.

The above is the detailed content of How to Efficiently Retrieve Multiple Sum Aggregations with Conditional Filtering in MySQL?. For more information, please follow other related articles on the PHP Chinese website!