COALESCE + Order By.. why does this happen?

[tdob]

Hi Everyone.

I have a question more to serve my curiosity/understanding. I have a MySQL statement that does some calculations on the fly, and I am using the coalesce function to return a 0 if there are no records.

Everything works, however I want to sort it in Descending order and only show the top 10. This kind of works, but it doesnt interpret my data right.

For example, lets say I have these values:
2
3
4
5
6
7
8
9
10
22
30

It will sort it in a fashion where it thinks 9 is the highest number, 8 is the second highest, and so on. It seems to only look at the first number of a double digit number. The column data type is INT(11).

Here is my command:

SELECT p.id
    , p.player
    , p.team
    , p.mugshot
    , coalesce(sum(s.goals),'0') as goals
    , coalesce(sum(s.assists),'0') as assists
    , coalesce(sum(s.fouls),'0') as fouls
    , coalesce(sum(s.assists + s.goals),'0') as points
    , t.teamname
FROM league as p
LEFT OUTER JOIN (SELECT goals
        , assists
        , fouls
        , player_id
    FROM stats) as s
    on s.player_id = p.id
LEFT OUTER JOIN (SELECT id
       ,teamname
     FROM teams) as t
     on t.id = p.team
GROUP
    BY p.id
    , p.player
ORDER BY points DESC
LIMIT 0,10

If I remove the COALESCE it works fine, but I like having it there incase there are nulls.

If anyone can help it would be appreciated.

[longneck]

take the quotes off the zeroes. a number inside quotes is a string and forces the entire column to sort as a string. a number outside of quotes is... (wait for it....) a number!

[tdob]

take the quotes off the zeroes. a number inside quotes is a string and forces the entire column to sort as a string. a number outside of quotes is... (wait for it....) a number!

*hangs head in shame*.

Thank You!