Data type conversion for AVG

Run the following script:
 
create table test (C1 int not null)
GO
INSERT  test values (1000000000)
INSERT test values (1000000001)
INSERT  test values (1000000002)
GO
 
What result the following query will give?
SELECT SUM(c1) from test
Yes. It gives value overflow error because the max integer is 2,147,483,647.
 
What about this query?
SELECT AVG(c1) from test
 
It gives value overflow error too. The reason is that SQL Server implements AVG as SUM/COUNT.

Now let’s create the table with a different data type: 
DROP TABLE test
GO
create table test (C1 tinyint not null)
GO
INSERT  test values (100)
INSERT test values (101)
INSERT  test values (102)
GO
 
The maximum value for tinyint is 255.
What’s the result of the following queries?
SELECT SUM(c1), AVG(C1) from test
 
It gives 301 and 101.
It does not give overflow error. When doing the aggregates, SQL server converts the value to int for all integer family data type (int, smallint, and tinyint). The aggregated value is of int.
 
But SQL does not convert int to bigint. So the first example always gets overflow error.
 
So if you are applying AVG to an int column you still have the chance to get value overflow error. You can convert the result bigint: SELECT cast(AVG(cast(c1 as bigint)) as int) from test
 
If you are applying AVG to a smallint or tinyint column you should never get value overflow error.
 
 
 
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

One Response to Data type conversion for AVG

  1. Youjin says:

    very useful tip for me 🙂 thx peter~!!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s