Getting Avg from Text Field 1

[mpemberton]

I've got an unusual problem, but I'm sure someone has an answer for me. My SQL table has a field I need to get an avg on, but unfortunately it is a varchar field type (which SQL doesn't like doing avg's on this type). I unfortunately cannot change the type in the table, because that field is an "answer" field which either has a response of 1-5, or a written text response.

Is there any workaround so I can do an avg on this field? Can I possibly change the type of the data in the Select statement? This query apparently works when accessing the data through an Access database (instead of going directly to the SQL table). I'm trying to convert it to eliminate the Access database. I'm running this through ASP pages if that helps.

Here is the current query, which doesn't work when running on SQL Server, but does work when running through Access:

SELECT SurveyResponse.QuestionText, Count(SurveyResponse.AnswerText) AS CountOfAnswerText, Avg(SurveyResponse.AnswerText) AS AvgOfAnswerText
FROM SurveyResponse
INNER JOIN SurveyQuestion
ON SurveyResponse.SurveyQuestion_ID = SurveyQuestion.SurveyQuestion_ID
WHERE SurveyResponse.SurveyMain_ID = strSurveyMain_ID
AND SurveyQuestion.QuestionType='Rating'
AND SurveyResponse.AddDate BETWEEN StartDate AND EndDate
GROUP BY SurveyResponse.QuestionText
ORDER BY Avg(SurveyResponse.AnswerText) DESC, SurveyResponse.QuestionText"

ANY Help would be awesome! Thanks in advance.

Mark

 

[sunila7]

Hi,

U can u use CAST or convert function to do what u r looking for... u can get help on CAST and convert on SQL Books online.
here i have put a eg to convert to integer data type... u can check SQL BOL and decide which data type u want to convert it to.

SELECT SurveyResponse.QuestionText, Count(SurveyResponse.AnswerText) AS CountOfAnswerText,
Avg(CAST(SurveyResponse.AnswerText AS INT)) AS AvgOfAnswerText
FROM SurveyResponse
INNER JOIN SurveyQuestion
ON SurveyResponse.SurveyQuestion_ID = SurveyQuestion.SurveyQuestion_ID
WHERE SurveyResponse.SurveyMain_ID = strSurveyMain_ID
AND SurveyQuestion.QuestionType='Rating'
AND SurveyResponse.AddDate BETWEEN StartDate AND EndDate
GROUP BY SurveyResponse.QuestionText
ORDER BY Avg(SurveyResponse.AnswerText) DESC, SurveyResponse.QuestionText

u can check this link for cast and convert help

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Sunil

 

[mpemberton]

AWESOME! Thanks for your expertise Sunil! That worked like a charm. Didn't know about the CAST function.