SQL: select average of varchar

Question

I would like to make a report that will show the average grade for different tasks.

I am having trouble with how to get the averages. I need to figure out how to convert the grades to floats so that I can take the averages. The grades sometimes have non-numeric or null values, although most values look like "2.0" or "3.5". I can exclude anything that is non-numeric.

This is what I have so far:

Select
GradingScores.task As task,
Avg(Cast((SELECT GradingScores.score WHERE GradingScores.score LIKE '%[^0-9]%')As float)) As averages
From
GradingScores 

I am using FlySpeed SQL Query.

Answer 1

You could try using IsNumeric

Select
GradingScores.task As task,
Avg(Cast(GradingScores.score as float) As averages
From
GradingScores where IsNumeric(GradingScores.score) = 1