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.
| 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 Query83.0714285714286Excel83.49547I'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]SELECTAVG(cast(a as float)) as AverageFROM( 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 |
 |
|
|
giszzmo
Starting Member
29 Posts |
Posted - 2015-05-08 : 13:41:19
|
| Thanks! There are no empty values in the grade column. |
|
|
|
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 |
|
|
|
giszzmo
Starting Member
29 Posts |
Posted - 2015-05-08 : 16:54:21
|
| Thanks! Good link. |
|
|
|
|
|
|
CAST vs Excel