[RESOLVED] MySQL SELECT query: Classify cell values?

[Joseph_Sliker]

Not sure if this is possible...

If I do a select query like this....

"SELECT NAME, SCORE FROM TABLE", I would like to add a column like this...

"SELECT NAME, SCORE, SCORE_CLASS FROM TABLE", such that a string value like "poor", "fair", "good" SCORE_CLASS will appear in the SCORE_CLASS column of the result set based on the score value:

ie.

If SCORE < 10 SCORE_CLASS = "Poor"

If SCORE >= 10 and < 20 SCORE_CLASS = "Fair"

If SCORE >= 20 SCORE_CLASS = "Good"

(example result set)
NAME SCORE SCORE_CLASS
Bobby 100 good
Billy 7 poor
Jenny 50 good
Julie 15 fair
Theophane 70 good

Can such a query be done in MySQL 3.23? I've been searching the MySQL online docs and have not found anything that seems to apply.

Thanks

[jheitz]

Hi there,

Not sure about MySQL. In SQL Server you can use a CASE statement so you could do this:

SELECT NAME, SCORE, CASE WHEN SCORE < 10 THEN "Poor" WHEN SCORE >= 10 AND SCORE < 20 THEN "Fair" ELSE "Good" END AS SCORE_CLASS FROM TABLE

Note: This might not be the exact syntax but this is possible.

If MySQL does not provide a similar functionality I would suggest you do this in Code (ie: get the Name and Score and through Code, make the check on Score and decide on the Score Class).

Good luck

[Joseph_Sliker]

That is BRILLIANT! I didn't think CASE was available in MySQL 3.23, but apparently it is, and it works great.

This is going to result in a quantum leap in the applications I've been building.

Thanks so much!

[jheitz]

Glad it helped