Kingston Dhasian (6/13/2013)

---

It happened because the data types for columns game1, game2,...game5 were taken as VARCHAR

The below code will avoid the issue

; WITH cte_Students AS

(

SELECT*,

CASE WHEN SUBSTRING(rate, 1, 1) = '*' THEN 10 /*The previous version had apostrophes here which made the data type of the column as VARCHAR*/ 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.0 /*Made 5 as 5.0 to avoid integer conversion*/ AS [percent]

FROMcte_Students

I am very grateful to you for your kind attention to my questions.

Let me tell you that the query works. But when returns the result it show me five numbers more.

For example the query returns:

Name game1 game2 game3 game4 game5 percent

Larry Walker 10 9 9 87 8.600000

I would like the results were like: 8.6, 9.0, 10, 8.8

thanks in advance.