| Author |
 Topic  |
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
 Posted - 10/11/2012 : 11:09:10
|
I have a table that is fed by an access database; the columns format unfortunatly has to be nchar and I can't find the way to calculate an AVG, while min(), max() works on that fields.
AVG(CAST(Temperature as real)) -> Error converting data type nvarchar to real
Martin |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/11/2012 : 11:39:42
|
You should be able to do this unless there is some data that is non-numeric. Typically, I have seen this happen when the data has thousands separators (3,745.33 etc.).
You can see if there is non-numeric data using this querySELECT * FROM YourTable WHERE ISNUMERIC(Temperature) = 0; But, ISNUMERIC is not 100 percent accurate for this purpose. For example, it won't catch the comma's that I referred to earlier. |
 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 10/11/2012 : 11:41:03
|
Let's go with the easy answer first. Do either of these produce any records?
SELECT *
FROM yourTable
WHERE Temperature like '%,%'
SELECT *
FROM yourTable
WHERE Temperature like '%[^0-9]%' and Temperature not like '%.%'
Jim
Everyday I learn something that somebody else already knew |
|
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
Posted - 10/11/2012 : 11:49:49
|
Thank you Jim, the komma got me on the right track... and even sunitabeck had the right hint in her answer, although I didn't question the numeric content until Jim's query ckecked the komma...
AVG(CAST(Replace(Temperature,',','.') as real)) does the tric
Martin |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 10/11/2012 : 12:04:56
|
Like Sunita said, ISNUMERIC isn't very unreliable, and I know Europe gets their commas and decimals backwards!
Jim
Everyday I learn something that somebody else already knew |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/11/2012 : 12:12:06
|
Good point about Europeans using comma and period for purposes opposite to what US does!
Sometimes, I put all the characters that I want to allow into the like clause - like this:SELECT CASE WHEN
col LIKE '%[^0-9. -E]%' THEN 'Nonnumeric' ELSE 'Numeric' END;
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 10/11/2012 : 12:41:26
|
Nice! I'm adding that to my UsefulThings folder!
Thanks,
Jim
Everyday I learn something that somebody else already knew |
|
|
| |
Topic |
|
nchar to real or to something I can get AVG from
