Solved

Why the difference comes in excel and sql server while using avg function,

Posted on 2013-06-11
5
214 Views
Last Modified: 2013-06-11
The sample data is attached.
When i find the average using excel for set of observations the output varies in excel and sql server.Difference--in-Output.xlsx
0
Comment
Question by:searchsanjaysharma
[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
  • 4
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39236946
it's (the result) is being treated as an integer in your query, but that's not true in Excel.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39236947
see the difference this way perhaps:

select avg(data), avg(data*1.0) from table1

http://sqlfiddle.com/#!3/2efbb/2
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 39236993
SELECT AVG(CAST(DataIn as REAL)) AS  FROM AVGTABLE
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39237021
yes, or any cast/convert that will provide higher precision, many to choose from...

select
  avg(data)
, avg(data*1.0)
, avg(cast(data as real))
, avg(cast(data as float))
, avg(cast(data as money))
, avg(cast(data as decimal(18,5)))
from table1
59  59.829545 59.829545454545 59.829545454545 59.8295  59.829545

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39237311
thanks & cheers, Paul
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I find this data? 3 41
the way to learn Microsoft BI 13 69
SQL Server for XML PATH giving wrong results. 6 59
EF5: Update Model from Database not working 3 79
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

732 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