?
Solved

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

Posted on 2014-09-09
6
Medium Priority
?
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 800 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 49

Accepted Solution

by:
PortletPaul earned 1200 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

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

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

765 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