Sum Nested Join Help

Transact-SQL
1

Hello all,

I have a query where I am trying to do a left join to gather a sum and this one seems not simple. Here is what I have:

SELECT C.CustomerId, C.TargetQty, C.GroupId, C.AcctCd
FROM Customer C

This is what I am needing to do and need the help. I have another table called CustomerTotals which has the following fields:
Table CustomerTotals
CustomerId, TargetQty, GroupId, AcctCd similar to the Customer table.

I need to Sum the TargetQty in this table and subtract it from the Select query Customer TargetQty Joined on GroupId and AcctCd where the CustomerId is not equal to the CustomerId in this totals table.

Example Data:
Table Customer
CustomerId, TargetQty, GroupId, AcctCd
1 500 1 AB

Table CustomerTotals
CustomerId, TargetQty, GroupId, AcctCd
1 500 1 AB
2 100 1 AB
3 50 1 AB

So I need the Select query to return such as:
SELECT C.CustomerId, C.TargetQty - (Sum value) As TargetQty, C.GroupId, C.AcctCd
FROM Customer C

The result would be:
CustomerId, TargetQty, GroupId, AcctCd
1 350 1 AB

The larger query I have has many fields with converts etc. so I am trying not to add the CustomerId to the nested query to get the sum somehow to not have to do the main query with a group by.
Thanks for any help

2

So even though the CustomerId in CustomerTotals is different, you count them all for CustomerId = 1?

3

use APPLY() operator

select c.CustomerId, c.TargetQty - t.TargetQty, c.GroupId, c.AcctCd
from   Customer c
       cross apply
       (
           select TargetQty = sum(t.TargetQty)
           from   CustomerTotals t
           where  t.CustomerId <> c.CustomerId
           and    t.GroupId = c.GroupId
           and    t.AcctCd  = c.AcctCd
       ) t

Or Join on not equal CustomerId and then GROUP BY

select c.CustomerId, c.TargetQty - sum(t.TargetQty), c.GroupId, c.AcctCd
from   Customer c
       inner join CustomerTotals t
       on  c.CustomerId <> t.CustomerId
       and c.GroupId    = t.GroupId
       and c.AcctCd     = t.AcctCd
group by c.CustomerId, c.TargetQty, c.GroupId, c.AcctCd