Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert string to decimal then average. Expand / Collapse
Author
Message
Posted Thursday, August 14, 2008 11:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 10, 2009 12:58 PM
Points: 9, Visits: 31
I need to convert a string to a decimal then average the results while ignoring null or "non-numeric like" values.

[color=CC0000]With this query, I can return the average but only if I convert null or strings other than 1-5 to "0".
AVG(CONVERT(decimal, CASE WHEN substring(hd1.workitem_comment, 115, 1) IN ('1','2','3','4','5') THEN substring(hd1.workitem_comment, 115, 1) ELSE '0' END)) AS 'Handled'[/color]

But with this one, I get an "error converting data type nvarchar to numeric
AVG(CONVERT(decimal(18,2), substring(hd1.workitem_comment, 115, 1))) AS 'Handled'


Any idea how to accomplish this?
Post #552945
Posted Thursday, August 14, 2008 11:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 944, Visits: 443
you've got the meat of it there... instead of doing the filtering in your avg() function, do it in the WHERE clause:

AVG(CONVERT(decimal(18,2), substring(hd1.workitem_comment, 115, 1))) AS 'Handled'
...
WHERE substring(hd1.workitem_comment, 115, 1) IN ('1','2','3','4','5')
Post #552952
Posted Thursday, August 14, 2008 1:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 10, 2009 12:58 PM
Points: 9, Visits: 31
That works great, Lenny. But one issue...how do I account for Null values. I don't want them to be included in the average. I'm not sure how to ignore those...that's sort of how I got to the initial query statement.
Post #553018
Posted Thursday, August 14, 2008 1:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 944, Visits: 443
you can add IS NOT NULL to the clause:
WHERE substring(hd1.workitem_comment, 115, 1) IN ('1','2','3','4','5') AND hd1.workitem_comment IS NOT NULL

or, if that doesn't work the way you want, if you can post some samples, i can give it another shot...

edit: just thought of something else, too:
WHERE substring(ISNULL(hd1.workitem_comment, ''), 115, 1) IN ('1','2','3','4','5')
Post #553022
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse