MySql SELECT query

Question

I am writing a script that calculates a Kill-Death Ratio (KDR) based on values obtained from a MySQL table. I am fixing somebody else's code. I have managed to get most of it working again. However, I have got stuck here. The KDR is calculated by adding all the kills, and dividing by the number of deaths of the player. But, if the player has never died, then the KDR comes out at 0. I need a way to read the 0, as a 1 when evaluating the KDR. I did google this, and tried to write a UDF I could use, but alas it did not help.

Below is the SQL query in question

function get_leaders($civ, $neu, $riv){
    $sSQL = "SELECT *, ((civilian_kills * _civ + neutral_kills * _neu + rival_kills * _riv) / deaths ) as KDR
              from sc_players
              order by KDR DESC
              LIMIT 100;";

You could write a MySQL Function to do that for you, or just do it in PHP. — Luke Pittman
– Luke Pittman, Commented Jun 24, 2012 at 19:40

Sean Johnson · Accepted Answer · 2012-06-24 19:48:42Z

4

http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#function_if

SELECT *,
IF(deaths,civilian_kills * $civ + neutral_kills * $neu + rival_kills * $riv) / deaths,1) as KDR
from sc_players
order by KDR DESC
LIMIT 100

edited Jun 24, 2012 at 19:48

answered Jun 24, 2012 at 19:43

Sean Johnson

5,6172 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trey Over a year ago

wouldn't it be SELECT *, IF(deaths==0,1,((civilian_kills * $civ + neutral_kills * $neu + rival_kills * $riv) / deaths))

Sean Johnson Over a year ago

yes, it would. For some reason I wasn't thinking in terms of ratios, good catch.

Stef Sijben · Accepted Answer · 2012-06-24 20:10:22Z

1

I think you mean the divisor should be treated as 1 when it is 0. In that case, you would do

SELECT *, ((civilian_kills * $civ + neutral_kills * $neu + rival_kills * $riv) / 
    (IF deaths = 0 THEN 1 ELSE deaths)
) as KDR from sc_players order by KDR DESC LIMIT 100;

Assuming deaths is nonnegative, you could also do:

SELECT *, ((civilian_kills * $civ + neutral_kills * $neu + rival_kills * $riv) / 
    GREATEST(deaths, 1)
) as KDR from sc_players order by KDR DESC LIMIT 100;

edited Jun 24, 2012 at 20:10

answered Jun 24, 2012 at 19:55

Stef Sijben

1531 silver badge8 bronze badges

3 Comments

Sean Johnson Over a year ago

You had the same initial understanding as I did in my solution, but if you give it a bit more thought - the KDR is the ratio of kills to deaths. If you treat the denominator as a 1 instead of a 0, you end up returning the number of kills the player has rather than the 100% ratio of kills to deaths as expected.

Stef Sijben Over a year ago

If you look at the original query in the case when deaths is positive, the answer is greater than 1 if the player has more kills than deaths, so I don't think you'd want to return 1 for someone who has never died.

Sean Johnson Over a year ago

True. I suppose what he wants is the average of kills per death, in which case your solution would be the correct one.

Collectives™ on Stack Overflow

MySql SELECT query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related