CODEWITHSUNDEEP
phpsql
brux
brux

Reputation: 3230

MySql SELECT query

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;";

Upvotes: 0

Views: 178

Answers (2)

Stef Sijben
Stef Sijben

Reputation: 153

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;

Upvotes: 1

Sean Johnson
Sean Johnson

Reputation: 5607

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

Upvotes: 4

Related Questions