select avg(mytinyint) from mytable

Msg 8115, Level 16, State 2, Line 6 
Arithmetic overflow error converting expression to data type int.

select avg(cast(mytinyint as bigint)) from mytable

declare @t table (id int) 
insert into @t select 1 
union all select 2147483647 
select avg(cast(id as bigint)) from @t -- works 
select avg(id) from @t -- fails

quote:

---

Return Types
The return type is determined by the type of the evaluated result of expression.

Expression result Return type 
integer category int 
decimal category (p, s) decimal(38, s) divided by decimal(10, 0) 
money and smallmoney category money 
float and real category float

---

quote:

---

kind of a waste of space if you ask me.

---

quote:

---

Originally posted by jezemine

Peso, I saw that in BOL too, but that's not the issue. It makes sense to return an int from avg() if you are averaging ints, or a byte if you are averaging bytes.

what I have a problem with is that internally, int is used to accumulate the count, which will overflow if the ints/smallints/tinyints you are averaging over sum to larger than MAX_INT. This issue has nothing to do with the return type however, because simple mathematics guarantees that the avg of any type T is always between MIN_T/MAX_T as I posted above, so it would always be safe to cast to T once the calc is finished internally.

does that make sense?

---

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

---

select
	avg(1000000000)
from
	f_table_number_range(1,100000)


Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.

quote:

---

Originally posted by snSQL

quote:

---

kind of a waste of space if you ask me.

---

No, what would be a waste of space is if they made it always use bigint as you suggest. They figured that int was big enough for most cases and you have to cast to bigint when you use a huge number. It has to sum up all your numbers before it can calculate the average, so it doesn't matter if the numbers are all one byte - the sum is huge, so the numbers used in calculating the sum must be huge.

---

quote:

---

Originally posted by Michael Valentine Jones
It sounds like your complaint is that SQL Server works exactly the way BOL says it works. What exactly would you expect?

---

quote:

---

I also don't imderstand why it should take you several hours to figure out an error like this:

select
	avg(1000000000)
from
	f_table_number_range(1,100000)


Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.

The error message seems fairly explicit and obvious to me.

---

quote:

---

Originally posted by snSQL

Your fyi is appreciated

You must remember though that SQL queries come in all shapes and sizes and SQL Server has to try and optimize each one is the best way possible while still staying generic enough to handle all the different things we throw at it. One of the ways that it does this is by having strict rules for how types are used in expressions. It's not about space, converting a number from one format to another even with complemetary types like int and bigint is nevertheless a conversion. So the designers of SQL Server made a choice to try and have few conversions as possible - so they left ints as ints. If you want to make the sum a big int, you have to convert the two numbers you're adding into bigints first, so if SQL Server did what you say then it would be converting your 700m ints into bigints anyway and you'd save nothing over your explicit conversion - BUT all the times when the sum is less than max-int would be converting unnecessarily.

We didn't assume you were SQL bashing, and we aren't jezemine bashing, just explaining why it works the way it does

---

quote:

---

perhaps the reasoning on the part of the dev was (as you seem to be hinting at) that the most common integer type column is a int32, and in that case no casts would be necessary if you accumulate in an int32. basically optimize for the most common case. If that was the reasoning I could buy it. :)

---

quote:

---

Originally posted by byrmol
How about the fact that SQL Server 7 never had a bigint. Upcasting to int was the only option.
Notice the addition of the bigint operators (COUNT_BIG etc..) that where added when SQL2k arrived.

---

quote:

---

Originally posted by Kristen

"I agree that it may be a rare case"

Not sure I agree its rare. You are at risk to the tune of:

2,147,483,647 / (Actual Average Value) being > Number of rows

So for 64K rows an average of 32K would be enough to break it.
At 1M rows the average drops to 2K
At 16M rows its down to 128

---

quote:

---

Originally posted by Peso

How would you calculate (2^63 - 1) + 1 ?
That's even to great for BIGINT, but every part is still less than max for BIGINT?

---

quote:

---

Originally posted by Peso
Jezemine, you DO NOT have to convert your table column to INT. As long as the calculated SUM does not exceed max for INT, it is ok to SUM up all TINYINTS too.

---