# SQL - SUM function problem

This sum is driving me nuts. It should add up all BeginQty and subtract it from am.BeginQty. But what I get is the results below. It is subtracting each line. What am I missing?

BeginQty      AllocRegion      MBeginQty      REMAINING
33      O      190      157
1255      W      190      -1065
20000      E      190      -19810

SELECT ag.BeginQty, ag.AllocRegion, am.BeginQty as MBeginQty, (am.BeginQty - SUM(IsNull(ag.BeginQty,0))) as REMAINING FROM CHISM_Allocation_Regions ag
RIGHT JOIN CHISM_Allocation_Main am on am.ID =  ag.AllocID
WHERE ag.AllocID = 6

GROUP BY ag.AllocRegion, am.BeginQty, ag.BeginQty
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You need to calculate the SUM then join.  Something like this.

``````SELECT
ag2.BeginQty,
ag2.AllocRegion,
am.BeginQty as MBeginQty,
(am.BeginQty - IsNull(ag.BeginQty,0)) as REMAINING
FROM
(SELECT AllocID, SUM(BeginQty) BeginQty FROM CHISM_Allocation_Regions GROUP BY AllocID) ag
RIGHT JOIN CHISM_Allocation_Regions ag2
ON ag.AllocID = ag2.AllocID
RIGHT JOIN CHISM_Allocation_Main am
on am.ID =  ag.AllocID
WHERE
ag.AllocID = 6
``````

Experts Exchange Solution brought to you by