Solved

how to use aggregate function or nvarchar( or whats the workaround?)

Posted on 2014-09-09
6
452 Views
Last Modified: 2014-09-15
I have a column with score of test. The datatype  is set to nvarchar. Now I have to create a report from the table which includes avg of specific range of records. I cant use the avg function on the column. Please help. Thanks
0
Comment
Question by:SQL .NET
  • 3
  • 2
6 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 200 total points
ID: 40313115
You should be able to cast your column to the data type you need (int, decimal) and then use the AVG function. However, you will also need to eliminate any non-numeric values by adding the ISNUMERIC function to your WHERE clause.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 40313264
You really do need to consider the data quality in such a situation, in particular for the range of records you want to average over: are there any where that score isn't numeric?

e.g.
select * from test_results where test_id = 1 and isnumeric(score) = 0 -- score is NOT numeric

if that is unacceptable you have to fix the data. Otherwise you can proceed as suggested by Shaun Kline above

e.g.
select AVG(CAST(score as int)) from test_results where test_id = 1 and isnumeric(score) = 1 -- score is numeric
0
 

Author Comment

by:SQL .NET
ID: 40314929
No the data has only numeric value and null value but the data type defined is nvarchar(50). Does Null value make the difference. Thanks Shaun AND Paul for the answers
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:SQL .NET
ID: 40317580
Can you please help with the use of convert function.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40318366
>>"Can you please help with the use of convert function."

what help do you need?

AVG(CAST(score as int))

AVG(CAST(score as decimal(6,2)))
0
 

Author Comment

by:SQL .NET
ID: 40324078
I have tried the cast but it gives error because it has decimal values too. And gives this error:Conversion failed when converting the nvarchar value '61.60' to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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