Solved

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

Posted on 2014-09-09
6
414 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now