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
jnordengAsked:
Who is Participating?
 
tim_csCommented:
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

Open in new window

0
 
jnordengAuthor Commented:
Excellent thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.