How to Correctly Aggregate Data from Multiple Tables in SQL Using Subqueries?

SQL Query to Aggregate Data from Multiple Tables

In SQL, aggregating data from multiple tables can be achieved using various techniques. Here's a scenario and a solution to a query that combines data from two tables:

Scenario:

Consider two tables, AP and INV, both containing columns [PROJECT] and [Value]. The goal is to retrieve a result that calculates the sum of [Value] for each project from both tables, resulting in the following format:

PROJECT | SUM_AP | SUM_INV
---------+--------+--------
XXXXX   | 500    | 800

Incorrect Query:

The following query was attempted but resulted in inaccurate sums:

SELECT AP.[PROJECT],
SUM(AP.Value) AS SUM_AP,
SUM(INV.Value) AS SUM_INV
FROM AP INNER JOIN INV ON (AP.[PROJECT] = INV.[PROJECT])
WHERE AP.[PROJECT] = 'XXXXX'
GROUP BY AP.[PROJECT]

Reason for Incorrect Results:

The incorrect query groups the values before applying the sum, leading to duplicate project values being included in the sums.

Correct Solution:

To address this, a query involving sub-selects can be employed:

SELECT
    AP1.[PROJECT],
    (SELECT SUM(AP2.Value) FROM AP AS AP2 WHERE AP2.PROJECT = AP1.PROJECT) AS SUM_AP,
    (SELECT SUM(INV2.Value) FROM INV AS INV2 WHERE INV2.PROJECT = AP1.PROJECT) AS SUM_INV
FROM AP AS AP1
    INNER JOIN INV AS INV1 ON (AP1.[PROJECT] = INV1.[PROJECT])
WHERE AP1.[PROJECT] = 'XXXXX'
GROUP BY AP1.[PROJECT]

This query utilizes sub-selects to calculate the sums within each project separately, eliminating the issue of duplicate values and providing the correct aggregate results.

The above is the detailed content of How to Correctly Aggregate Data from Multiple Tables in SQL Using Subqueries?. For more information, please follow other related articles on the PHP Chinese website!