CODEWITHSUNDEEP
sql-serverunionsum
IMAbev
IMAbev

Reputation: 188

SQL Union Query does not Sum correctly

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

Answers (2)

Anil Soman
Anil Soman

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

Paul Keister
Paul Keister

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

Related Questions