CODEWITHSUNDEEP
sqlsql-servercastingaveragevarchar
Genevieve Ashymov
Genevieve Ashymov

Reputation: 43

SQL: select average of varchar

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.

Upvotes: 3

Views: 11839

Answers (3)

dizad87
dizad87

Reputation: 488

here's what worked for me:

SELECT AVG(CAST(columnName AS FLOAT)) FROM tableName;

Upvotes: 0

Ry Rith
Ry Rith

Reputation: 117

Just simply convert() and use isnumeric() function in T-SQL

select avg(convert(float,score))
from GradingScores 
where isnumeric(score)=1

Upvotes: 1

etoisarobot
etoisarobot

Reputation: 7814

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

Upvotes: 2

Related Questions