Solved

SQL Server AVG Function Data Type Restriction

Posted on 2011-09-22
2
304 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

789 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