Solved

SQL Server AVG Function Data Type Restriction

Posted on 2011-09-22
2
301 Views
Last Modified: 2012-05-12
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?

Thanks all.

-- Rick
0
Comment
Question by:MinnRick
2 Comments
 
LVL 5

Accepted Solution

by:
Eduardo Goicovich earned 500 total points
ID: 36584174
use convert function to meet your needs

example

INSERT INTO tablex (fieldNumeric(6,2))
SELECT
AVG(
CONVERT(decimal(18,4),smallintSourceField))
FROM tabley
0
 

Author Closing Comment

by:MinnRick
ID: 36584372
Yes, works perfectly.  Thanks very much belfegor.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now