SQL Server Performance Forum – Threads Archive
AVG on nvarchar fields
Hi all.Is there any way to perform a aggragate function, AVG, MIN, MAX on field type of nvarchar ? I know cast and convert may be options, but I am having trouble finding examples. Also, there are some cases where nulls are allowed in the data field.
thanks.
select sum(cast(a as float)) from b
select sum(convert(float,nvarcharcolumn)) from yourtable
Null will be omitted automatically
Madhivanan Failing to plan is Planning to fail
Why would you want to do calculations like these on non-numerical columns?
If you intend to do such operations you should choose a more appropriate data type like INT, DECIMAL or FLOAT. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
yes I also agree with the suggestion
If you have the control over the database you can change the filed type to int or float
Performance wise it will be better to have filed in numeric data type
quote:Originally posted by FrankKalis
Why would you want to do calculations like these on non-numerical columns?
If you intend to do such operations you should choose a more appropriate data type like INT, DECIMAL or FLOAT.
If you intend to do such operations you should choose a more appropriate data type like INT, DECIMAL or FLOAT.
Yes, I agree. However, this table has type nvarchar on ALL fields regardless of their contents. To make matters worse, many NULLs exist in many of the fields I need to aggregate.
Thanks.
quote:Originally posted by FrankKalis
Why would you want to do calculations like these on non-numerical columns?
If you intend to do such operations you should choose a more appropriate data type like INT, DECIMAL or FLOAT. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
If you intend to do such operations you should choose a more appropriate data type like INT, DECIMAL or FLOAT. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
Can’t you change the varchar to int or float
Vendor driven, no choice.
quote:Originally posted by dineshasanka
Can’t you change the varchar to int or float
unfortunate,<br />can’t you educate him. you have good case to show, show this post.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by kdefilip</i><br /><br />Vendor driven, no choice.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
]]>