Solved

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

Posted on 2013-06-11
5
212 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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2008 R2 syntax 11 36
how to import result to CVS from sql server management studio without comma 2 29
While in ##Table - Help 4 19
query optimization 6 15
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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