CODEWITHSUNDEEP
sqlsumleft-join
nth
nth

Reputation: 337

SQL Left Join doubling value of summed columns

I've got 2 tables that I need query and return a result set of "exceptions" based upon if the sum of a field in table1 equals the sum of the field in table2 (where 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 isn't correct because it multiplies the Table1.TranQty sum by the number of rows returned from Table2.

For example if Table1 had 1 record where the join matched 2 records in Table2, the TranQty from the 1 record in Table1 would be multiplied by 2 (and thus matched incorrectly).

I'm sure I'm missing something basic about using an aggregate function (sum) in a left join.

Thanks for the help!

(System is MSSql)

Upvotes: 4

Views: 6318

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74315

It's doing exactly what you asked it to do (but that's something other than what you expected it to do). 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 show up that many times.

In looking at your original SQL, it would appear that what you're trying to accomplish is this:

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 that are each summaries.

Upvotes: 0

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18818

Try this query..Basically, you should aggregate A's and B's results seperately and verify the counts 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 the combination of these three columns is enough to define a row in A and B. If there are additional rows, you might need to add them too.

Upvotes: 6

Related Questions