Seemingly incorrect sum of records when grouped. What's happening?

[colewillm]

Hello,

I'm having a problem that is throughout my database, but I will limit it to one example in this post. I'm running a query on records with ID '100' which returns two pieces of data both with EARNED_PREMIUM_USD ($360.01). Logically you could expect the sum of these to be ($720.02), but on the aggregate sum query it returns ($923.23). What is going on here?

I will post the SQL code for the two queries below.

SELECT dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.EARNED_PREMIUM_USD​

FROM dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP​

WHERE (((dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.ID)=100));

SELECT Sum(dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.EARNED_PREMIUM_USD) AS SumOfEARNED_PREMIUM_USD
FROM dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP
WHERE (((dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.ID)=100));





​

 

[Joe4]

It is very hard to figure out what may be going on without seeing the data.
Are there any duplicates in your data?
Do you have any NULL values in the ID field?

Does this make any difference?

SELECT Sum(dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.EARNED_PREMIUM_USD) AS SumOfEARNED_PREMIUM_USD
FROM dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP
WHERE dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.ID=100
GROUP BY dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.ID;

 

[xenou]

Just for kicks, post exactly the results of these two queries:

SELECT 
	dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.EARNED_PREMIUM_USD
FROM 
	dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP
WHERE 
	dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.ID = 100;
	
SELECT 
	Sum(dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.EARNED_PREMIUM_USD) AS SumOfEARNED_PREMIUM_USD
FROM 
	dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP
WHERE 
	dbo_XLOAD_PREMIUM_DATABASE_FC_FINAL_CMP.ID = 100;