Using cast inside sum or outside ?

Question

I work on sql server 2012 I face issue using cast first or sum ?
cast (sum(a) as decimal(18,2)
or
sum(cast (a as decimal(18,2)))

which is correct and more speed

a is integer need to convert to decimal

so using cast inside sum or outside

Answer 1

Just to be clear.

In your specific example, you can do an outer cast.

However, if you were going the other way, decimal to int, you would get different results depending on where you do that cast. Only you can determine which is correct.

DECLARE @testtbl TABLE (nums DECIMAL(18,2))
INSERT INTO @testtbl VALUES 
(1.1),
(2.2),
(3.322),
(4.44)


SELECT CAST(SUM(nums) AS INT)  -- = 11
FROM @testtbl


SELECT SUM(CAST(nums AS INT)) -- = 10
FROM @testtbl

Answer 2

In my opinion, if you have 1000 numbers, for example, then it is better to have a single cast instead of 1000 casts. Therefore, “cast(sum(a) as decimal” (one cast) seems better than “sum(cast(a) as decimal)” (1000 casts).

Answer 3

If you want the result to be decimal(18,2), you should use

cast(sum(a) as decimal(18,2))

which will give you as decimal(18,2) result. But the result of

sum(cast(a as decimal(18,2)))

will give you as decimal(38,2) result. If you run

create table #Fooa(a int);  
insert #Fooa(a) values(1), (2), (3);  
select sum(cast(a as decimal(18,2))) as x into #Foob from #Fooa;  
exec sp_help #Foob;  
go  
drop table #Fooa;  
go  
drop table #Foob;  

you will see the result has precision of 38, not 18.

Also, I agree with @Viorel that casting the sum will be faster the summing the cast (although I think the difference in performance will be very small). Therefore, for both reasons, you should go with casting the sum.

Tom

Answer 4

You should cast the result to the target type, but only if you're certian that aggregate won't overflow its return type.

https://learn.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-ver15#return-types

Answer 5

You should cast the result to the target type, but only if you're certian that aggregate won't overflow its return type.

It seems reasonable to assume that it will, since else why do we want 16 digits before the decimal point?

Given Tom's post, the best is maybe

cast(sum( cast(a as decimal(18,2)) decimal(18,2)

Just so that Ahmed talks about, if you sum integer values, the result cannot exceed 2.147 milliards, since that is the upper limit of an int. Therefore you need to convert inside the sum to avoid that problem.