Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

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

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
SQL .NET
Asked:
SQL .NET
  • 3
  • 2
2 Solutions
 
Shaun KlineLead Software EngineerCommented:
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
 
PortletPaulCommented:
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
 
SQL .NETAuthor Commented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
SQL .NETAuthor Commented:
Can you please help with the use of convert function.
0
 
PortletPaulCommented:
>>"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
 
SQL .NETAuthor Commented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now