Sum Nested Join Help

Question

Sum Nested Join Help

beantownace

Old Hand

Points: 333
More actions
July 16, 2024 at 4:03 pm

#4436707
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
- This topic was modified 4 months, 1 week ago by beantownace.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jmetape Right there with Babe Points: 731 More actions · Answer 1

I threw some data into temporary tables and came up with this:

SELECT customerID, SUM(TargetQty) , SUM(TargetQty) - (SELECT sum(targetqty) from #customerTotals t WHERE t.customerID <> c.customerID) TargetQtySubtractOthers, groupID, acctcD

FROM #customer c

GROUP BY customerID, groupID, acctCD

Here is the data population:

drop table if exists #customer 
SELECT 1 as customerID, 500 as TargetQty, 1 as groupID, 'AB' as acctcD 
INTO #customer
UNION 
SELECT 2 as customerID, 50 as TargetQty, 1 as groupID, 'AB' as acctcD 
UNION 
SELECT 2 as customerID, 50 as TargetQty, 1 as groupID, 'AB' as acctcD 
UNION 
SELECT 3 as customerID, 50 as TargetQty, 1 as groupID, 'AB' as acctcD



drop table if exists #customerTotals
SELECT 1 as customerID, 500 as TargetQty, 1 as groupID, 'AB' as acctcD
INTO #customerTotals
UNION SELECT 2, 100, 1, 'AB'
UNION SELECT 3, 50, 1, 'AB'




SELECT customerID, SUM(TargetQty) , SUM(TargetQty) - (SELECT sum(targetqty) from #customerTotals t WHERE t.customerID <> c.customerID) TargetQtySubtractOthers, groupID, acctcD 
FROM #customer c 
GROUP BY customerID, groupID, acctCD

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

beantownace wrote:

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

Is the expected output 3 rows or 1 row? If it's 3 one approach might be to use OUTER APPLY with a subquery

SELECT c.*, oa.sum_tqty, c.TargetQty-oa.sum_tqty t_minus_sum
FROM #customer c 
     outer apply (select sum(t.TargetQty)
                  from #customerTotals t
                  where t.groupID=c.groupID
                        and t.acctcD=c.acctcD
                        and t.customerID <> c.customerID) oa(sum_tqty);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

jmetape Right there with Babe Points: 731 More actions · Answer 3

Thanks Steve Collins--I missed the "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"