Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

571 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