August 14, 2008 at 11:32 am
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?
August 14, 2008 at 11:44 am
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')
August 14, 2008 at 1:20 pm
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.
August 14, 2008 at 1:24 pm
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