Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 CAST vs Excel

Author  Topic 

giszzmo
Starting Member

29 Posts

Posted - 2015-05-08 : 11:52:12
Hello,
I am casting a varchar to a float and then averaging it.
avg(cast(GRADE as float))


To verify my averages I took the list of numbers to Excel and averaged them there. I'm off by a few decimal points so I'm not sure if its happening in the converting or in Excel.

Results:
TSQL Query
83.0714285714286

Excel
83.49547

I'm not sure what is happening behind the scenes. Should I have used a different way of converting? If anyone could point me in the right direction. Thanks!

jleitao
Posting Yak Master

100 Posts

Posted - 2015-05-08 : 13:10:12
do you have empty values in Grade Column?

Excel AVERAGE function don't count with non numeric cells (or empty cells) to the final result.

for example, if you execute the next code, you see that SQL count with empty cells (as 0) to average, however if you try the same in EXCEL you result will be 15:

[CODE]
SELECT
AVG(cast(a as float)) as Average
FROM(
select '10' as a
union
select '20' as a
union
select '' as a
) T
[/CODE]

I don't know what you need but if you add WHERE GRADE <> '' i think that you result will be the same in SQL and EXCEL.

If not, post a few sample of data.

------------------------
PS - Sorry my bad english
Go to Top of Page

giszzmo
Starting Member

29 Posts

Posted - 2015-05-08 : 13:41:19
Thanks! There are no empty values in the grade column.
Go to Top of Page

jleitao
Posting Yak Master

100 Posts

Posted - 2015-05-08 : 13:46:32
how many decimal digits you have?

take a look: https://technet.microsoft.com/en-us/library/ms173773(v=sql.105).aspx

------------------------
PS - Sorry my bad english
Go to Top of Page

giszzmo
Starting Member

29 Posts

Posted - 2015-05-08 : 16:54:21
Thanks! Good link.
Go to Top of Page
   

- Advertisement -