Convert string to decimal then average.

  • 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?

  • 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')

  • 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.

  • 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')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply