SQL Server AVG Function Data Type Restriction
Posted on 2011-09-22
Hello Experts -
I have a SQL Server 2008 R2 table with a column of type SMALLINT on which I would like to calculate (and drop into a different table) a variety of averages (presumably using the AVG function). The calculated averages won't always be integers so I'm expecting to use a NUMERIC(6, 2) data type for the results in the second table. If I am reading the data typing detail for the AVG function correctly, it sounds like I can't use the SMALLINT data type on the source field if I want the calculated value to be NUMERIC. This strikes me as absurd - it's easy to envision many scenarios where you'd start with SMALLINTs and then calculate results that are NUMERIC. Such a rule would force a re-typing requirement that simply seems unnecessary. Two questions:
1. Is my understanding of the data typing for the source field called by the AVG function correct?
2. If so, are there any straightforward workarounds that will not require me to re-type my source field from INT to NUMERIC, even though that field only holds INT values?