Reputation: 188
I have a Union query with 4 subquerys. For example:
(Select Sum(x.result)
FROM
(
<query 1>
UNION
<query 2>
UNION
select IsNull(Sum(A.TransAmount) + Sum(Case When A.BetResult = 1 Then (A.BetWinAmount + (A.TransAmount * -1)) End), 0) As Result
from <join omitted>
where <where omitted>
) As X
The problem is in the Union with the "Sum". What I am trying to do there is sum a.transamount and sum other columns (calculated) when betresult = 1. When I run the query the calculation is not correct.
When I pull this Union out of the statement and run it by itself, it returns the correct value. INSIDE the Union it does not calculate BOTH sum's.
Does UNION ignore this type of multiple sums in one of the statements or do I have something else wrong?
Upvotes: 1
Views: 3167
Reputation: 2467
Try using CTE and then select SUM from that table. This may casue it to consider a single table to take proper sum..
;WITH SUMTab as
(
<query 1>
UNION
<query 2>
UNION
select IsNull(Sum(A.TransAmount) + Sum(Case When A.BetResult = 1 Then (A.BetWinAmount + (A.TransAmount * -1)) End), 0) As Result from <join omitted> where <where omitted>
)
SELECT SUM(result) from SUMTab
....
Upvotes: 0
Reputation: 13097
By default the UNION keyword uses the DISTINCT modifier. Since SUM also supports DISTINCT, I'm guessing that what's happening is that you are only summing the distinct values in your query. Try using UNION ALL instead of UNION and see if the SUM results are correct.
Upvotes: 6