Kingston Dhasian (6/12/2013)

---

You can use a CTE or a Derived table like this

; WITH cte_Students AS

(

SELECT*,

CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 1, 1) END AS game1,

CASE WHEN SUBSTRING(rate, 2, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 2, 1) END AS game2,

CASE WHEN SUBSTRING(rate, 3, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 3, 1) END AS game3,

CASE WHEN SUBSTRING(rate, 4, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 4, 1) END AS game4,

CASE WHEN SUBSTRING(rate, 5, 1) = '*' THEN '10' ELSE SUBSTRING(rate, 5, 1) END AS game5

FROMstudents

)

SELECT*, ( game1 + game2 + game3 + game4 + game5 ) / 5 AS [percent]

FROMcte_Students

Thanks for you prompt response. Let me tell you that the query has showed a diferent result.

for example:

id name game1 game2 game3 game4 game5 percent

3Larry Walker 10998721997

the result should be 8.6

or may be I am doing something wrong.

I appreciate your attention.