My SQL ORDER BY is incorrect

Junior WebHelper Joined: 14 Feb 2007 Posts: 1

Im trying to sort by ratio (which is generated by (kills/deaths+1)).

SELECT DISTINCT name, SUM(kills), SUM(deaths), ROUND(SUM(kills) / (SUM(deaths)+1),2), COUNT(name)

FROM player WHERE name LIKE '%TuG%'

GROUP BY name

ORDER BY ROUND((SUM(kills) / (SUM(deaths)+1)),2) DESC, SUM(kills) DESC

I also used ORDER BY SUM(kills) / (SUM(deaths)+1) DESC, SUM(kills) DESC and added () around but it still doesnt work.

This almost works fine, apart from incorrectly displaying the ratio (kills/deaths+1) in order. There is ways round this (putting into a an array in php and sorting it there), but I want to use sql to correctly sort it.

Thses are my output of results (the db does have around 40000 records)
http://www.brok21k.co.uk/testsql.php

john

Posted: Sat Feb 17, 2007 10:40 am (17 years, 10 months ago)

I would suggest simplifying it to this:

Code:

SELECT DISTINCT name, SUM(kills), SUM(deaths), ROUND(SUM(kills) / (SUM(deaths)+1),2) as ratio, COUNT(name)

FROM player WHERE name LIKE '%TuG%'

GROUP BY name

ORDER BY ratio DESC

Not sure if that'll fix it or not, but either way there's no sense in doing that calculation twice.

Posted: Sat Feb 17, 2007 10:44 am (17 years, 10 months ago)

In fact, you might even be able to simplify it further:

Code:

SELECT DISTINCT name, SUM(kills) as s_kills, SUM(deaths) as s_deaths, ROUND(s_kills / (s_deaths +1) ),2) as ratio, COUNT(name)

FROM player WHERE name LIKE '%TuG%'

GROUP BY name

ORDER BY ratio DESC

Though I'm not sure if that is valid SQL.