SQL Left Append Doubling Value of Summarized Columns

Question

I have 2 tables in which I need a query, and return a result set of "exceptions" based on whether the sum of the field in table1 is equal to the sum of the field in table2 (where the other columns match).

select A.TranName,A.TranDate,A.TranCode,SUM(A.TranQty) AS T1Qty,B.TranName,B.TranDate,B.TranCode,SUM(B.TranQty) AS T2Qty from Table1 AS A LEFT JOIN Table2 AS B on A.TranName = B.TranName AND A.TranDate = B.TranDate AND A.TranCode = B.TranCode GROUP BY A.TranName, A.TranDate, A.TranCode, B.TranName, B.TranDate, B.TranCode HAVING SUM(A.TranQty) != SUM(B.TranQty) 

The result set is incorrect because it multiplies the sum of Table1.TranQty by the number of rows returned from table 2.

For example, if in table 1 there was 1 record in which the connection was mapped to 2 records in table 2, the TranQty of 1 record in table 1 would be multiplied by 2 (and, accordingly, mapped incorrectly).

I am sure that I am missing something in common in using the aggregate function (sum) in the left join.

Thanks for the help!

(System is MSSql)

Answer 1

Try this request. Basically, you should aggregate the results of A and B separately and check the counters after that.

 select a.TranName,a.TranDate,a.TranCode from ( select TranName,TranDate,TranCode, SUM(TranQty) AS T1Qty Table1 group by TranName,TranDate,TranCode) a LEFT JOIN ( select TranName,TranDate,TranCode, SUM(TranQty) AS T1Qty Table2 group by TranName,TranDate,TranCode) b on (A.TranName = B.TranName AND A.TranDate = B.TranDate AND A.TranCode = B.TranCode) where a.T1Qty != b.T1Qty 

Make sure that the combination of these three columns is sufficient to define row A and B. If there are additional rows, you may also need to add them.

Answer 2

This does exactly what you asked him to do (but it is something different than what you expected from him). Each row from table 1 will be repeated at least once in the result set. If it matches more than 1 row in table 2, then it will be displayed many times.

Looking at the source SQL, it looks like you are trying to do the following:

 select * from ( select TranName , TranDate , TranCode , TranQTy = sum(TranQty) from Table1 group by TranName , TranDate , TranCode ) A full join ( select TranName , TranDate , TranCode , TranQTy = sum(TranQty) from Table2 group by TranName , TranDate , TranCode ) B where ( A.TranQty is null and B.TranQty is not null OR A.TranQty is not null and B.TranQty is null OR A.TranQt != B.TranQty ) 

You want to find the difference between two sets, each of which is brief.