Sql-server – SQL Compare data from two joined tables

join;sql serversqlcompare

i used SQL Server, and have two joined table

Select b.team, SUM(a.total) FROM data1 A 
INNER JOIN team b ON a.teamID = b.teamID
WHERE month = 1 and year = 2019
GROUP by team

second one is like this

Select b.team, SUM(c.total) FROM data2 C 
INNER JOIN team b ON c.teamID = b.teamID
WHERE Date BETWEEN '2019-01-01' AND '2019-01-31'
GROUP by team

and the result will be :

Data 1                   Data 2
TEAM   | Total        TEAM   | Total
Team 1 | 5            Team 1 | 4
Team 2 | 3            Team 2 | 3
Team 3 | 8            Team 3 | 9

i wanted to create query to get result like this :

Team   | Total
Team 1 | 1
Team 3 | -1

Best Answer

There's a bit of ambiguity in the original spec, as in the example, each team is in both lists, yet it isn't clear whether this will always be the case. If it isn't, should a missing value be treated as zero, or does it imply the team should be dropped from the results, like when the net total is zero?

If the latter, an inner join would be appropriate:

SELECT d1.Team, Total = d1.Total1 - d2.Total2
FROM (
    SELECT b.Team, Total1 = SUM(a.total)
    FROM data1 A 
    INNER JOIN team b ON a.teamID = b.teamID
    WHERE month = 1 and year = 2019
    GROUP BY Team
) d1
INNER JOIN (
    SELECT b.Team, Total2 = SUM(c.total)
    FROM data2 C 
    INNER JOIN team b ON c.teamID = b.teamID
    WHERE Date BETWEEN '2019-01-01' AND '2019-01-31'
    GROUP BY Team
) d2
ON d2.Team = d1.Team
WHERE d1.Total1 <> d2.Total2;

If the former, a full outer join:

SELECT Team = ISNULL(d1.Team, d2.Team), Total = ISNULL(d1.Total1, 0) - ISNULL(d2.Total2, 0)
FROM (
    SELECT b.Team, Total1 = SUM(a.total)
    FROM data1 A 
    INNER JOIN team b ON a.teamID = b.teamID
    WHERE month = 1 AND year = 2019
    GROUP BY Team
) d1
FULL JOIN (
    SELECT b.Team, Total2 = SUM(c.total)
    FROM data2 C 
    INNER JOIN team b ON c.teamID = b.teamID
    WHERE Date BETWEEN '2019-01-01' AND '2019-01-31'
    GROUP BY Team
) d2
ON d2.Team = d1.Team
WHERE ISNULL(d1.Total1, 0) <> ISNULL(d2.Total2, 0);
Related Question