Solved

SQL Server AVG Function Data Type Restriction

Posted on 2011-09-22
2
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Downgrading MS SQL 2008 R2 Enterprise to MS SQL 2005 Standard? 12 95
If in a where clause in t-sql 7 53
MSSQL Convert Char to Date Time 5 36
t-sql left join 2 32
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

734 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