?
Solved

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

Posted on 2013-06-11
5
Medium Priority
?
216 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 49

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 49

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 49

Accepted Solution

by:
PortletPaul earned 1500 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 49

Expert Comment

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

770 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