question

Gopu_CS avatar image
Gopu_CS asked

In Which order summation is done in SQL Server 2008

select sum(Quantity) from tblXYZ where Condition1 and Condition2 and Live=1 Here Quantity column is integer When i am running above query is runs fine sometime and sometime it fails. Error: "Arithmetic overflow error converting expression to data type int." I know it fails due to intermediate result is greater than INT32.MAX value. If i change it to BIGINT it will solve issue. But i don't want to change it to BIGINT. Here tblXYZ has Date column which is date time, which tells, when all transactions are inserted If i do summation in that order then i think i can resolve the issue. Any help on this is appriciated.
sql-server-2008intoverflow
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
The order of row processing is not guaranteed and you canot override it by any documented way. In general it depends on the query pland and degree of parallelism. If you do not want to change the underlying data type, you can cast it in your query. select sum(CAST(Quantity AS bigint)) from tblXYZ where Condition1 and Condition2 and Live=1
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pretty sure Pavel gave you the same answer... just make your result a BIGINT in the SELECT clause, not your table's entries. I'd suggest you mark it as correct so others can easily see that your issue was resolved.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.