How to Retrieve Two Distinct Data Sets with Conditional Aggregation in a Single SQL Query?

Efficiently Retrieving Two Separate Data Sets Using a Single SQL Query

This example demonstrates how to retrieve two distinct datasets from a single SQL query, avoiding the need for multiple queries. We'll use a transactions table (containing id, account_id, budget_id, points, and type columns) to illustrate. The goal is to obtain:

1. The total points for each budget_id where type is 'allocation'.
2. The total points for each budget_id where type is 'issue'.

The Solution: Conditional Aggregation

A single SQL query can achieve this using conditional aggregation:

<code class="language-sql">SELECT 
    budget_id,
    SUM(CASE WHEN type = 'allocation' THEN points ELSE 0 END) AS allocated_points,
    SUM(CASE WHEN type = 'issue' THEN points ELSE 0 END) AS issued_points
FROM 
    transactions
GROUP BY 
    budget_id;</code>

This query employs the CASE statement (or IF in some database systems) to conditionally sum the points. If type is 'allocation', the points are added to allocated_points; otherwise, 0 is added. The same logic applies to issued_points. The GROUP BY clause ensures that the sums are calculated for each unique budget_id.

Expected Output:

The query will return a result set similar to this:

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

The above is the detailed content of How to Retrieve Two Distinct Data Sets with Conditional Aggregation in a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!