Game Kills against Deaths

MySQL database
#1

I am probably missing something really obvious here, but I am attempting to make a code to show the highscore for a game where you kill other players/monsters. The highscore is supposed to show the top 15 players by average number of kills per death that they have, but I can’t find a nice why to do this with the database information I have.

The database stores records by each kill or death is 1 record into the ‘killschart’ table. The table itself looks like this:

http://la.wrence.in/u/081115042.png (It didn’t allow me to include an image/link, so I have just written to it)

Where the “killed_by_uuid” is set when a player (using the value of the box) kills something, and then the “killed_uuid” is set when the player (using the “killed_uuid” value) is set when a player dies for whatever reason. The other data isn’t important to what I am trying to show here.

I can get it to show the values for kills and deaths separately because I simply group by the “killed_by_uuid” and “killed_uuid” respectively and then COUNT(*) the number of records for each. However I am now looking to actually compare the number of kills a player gets against how many times they have died, and order the results according to that number. (The player uuid’s are the same for each column incase that wasn’t clear)

Sorry if my explanation isn’t what it’s meant to be, if anyone needs more information please contact me; I am normally fine with SQL stuffs; but have just reached a wall with this.

Thanks so much to anyone who can help me.

#2

Hello,

wouldn’t a query like this work for you?

SELECT TOP 15 `killed_by_uuid` FROM `killschart`
#3

To put it basically: no. That would simply select the top 15 killers as shown in the database? I need to select them according to the kills divided by the deaths (both of which are individual records in the same table, with different identifying uuid records).

I’m not even sure what I want to do is possible. I need a way to group to different columns together. Like:
If username is in killed_by_uuid OR killed_uuid take the record and count them, but then if in killed_by add to kills and if not add to deaths and then divide and order.

#4

i was looking at the image u posted, are the values for killed_by_uuid encrypted somehow?

#5

They are an encrypted version of the actual username, they connect to another table which contains the actual user data, I don’t have an issue with them though, if for the purpose of doing this we take the values in killed_by_uuid and killed_uuid as the actual names that would be fine.

I can fetch the proper names later in the process after the top 15 selection has already been done.

#6

I don’t quite understand what you want to do sorry.

maybe add me on skype and you can explain me better what you are trying to do.

#7

I did it alone, thanks for the help though.

Incase anyone wonders what I did:

SELECT *, SUM(CASE WHEN killed_by_uuid != '' THEN 1 ELSE 0 END) AS kills, SUM(CASE WHEN killed_uuid != '' THEN 1 ELSE 0 END) AS deaths, (CONCAT(killed_by_uuid, '', killed_uuid)) AS userfile, ((SUM(CASE WHEN killed_by_uuid != '' THEN 1 ELSE 0 END))/(SUM(CASE WHEN killed_uuid != '' THEN 1 ELSE 0 END))) AS scores FROM killchart GROUP BY userfile ORDER BY scores DESC LIMIT 15

Basically it merges the killed_by_uuid and the killed_uuid columns into what I have named “userfile” and then groups by that and counts the total occurrences of a value being a kill and a value being a death, divides them by each other and orders by that. :slight_smile:

Sponsor our Newsletter | Privacy Policy | Terms of Service