How Can a Single SQL Query Retrieve Distinct Sums Based on Multiple WHERE Clause Conditions?

Use multiple WHERE clauses to retrieve different data

In SQL, you can use the WHERE clause to filter specific rows, thereby customizing how data is retrieved from a table. This problem presents the challenge of extracting two different data sets from the same table, each satisfying a different WHERE clause.

The example table "transactions" stores records of financial transactions characterized by their ID, account ID, budget ID, points earned, and transaction type. The task is to calculate the sum of allocated points and the sum of issued points for each unique budget ID.

To do this, a single SQL query can be constructed that combines the necessary WHERE clauses. The following query achieves the desired result:

<code class="language-sql">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</code>

This query uses the CASE WHEN statement to conditionally sum points based on transaction type. For each budget ID, the query calculates the sum of points associated with the "allocation" type and stores it in the "allocated" column. Similarly, it calculates the sum of points of type "issue" and stores it in the "issued" column. By grouping the results by budget ID, the query produces the expected result set, which contains separate columns for allocated points and issued points for each budget.

This query demonstrates the flexibility of SQL in combining WHERE clauses to retrieve specific subsets of data from a table, making it a powerful tool for performing complex data retrieval tasks.

The above is the detailed content of How Can a Single SQL Query Retrieve Distinct Sums Based on Multiple WHERE Clause Conditions?. For more information, please follow other related articles on the PHP Chinese website!