Here is what I did to get it to work.

Statsdeath.select("player SUM(amount)").group(:player).order("SUM(amount) DESC").limit(1).sum(:amount)

It is however returning a Hash that is hard to work with. Instead of the keys being player and amount, they are the player's name and the amount.

{"foo"=>73}

Is this the best way to associate a player with their amount of deaths?

Controller

@mostKilledplayer = Statsdeath.select("player SUM(amount)").group('player').order("SUM(amount) DESC").first

Error: 
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(amount) FROM `Stats_death`  GROUP BY player ORDER BY SUM(amount) DESC LIMIT ' at line 1: SELECT  player SUM(amount) FROM `Stats_death`  GROUP BY player ORDER BY SUM(amount) DESC LIMIT 1

View

Most Killed Player: <%=@mostKilledplayer.first%>

Error: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(amount) FROM `Stats_death`  GROUP BY player ORDER BY SUM(amount) DESC LIMIT ' at line 1: SELECT  player SUM(amount) FROM `Stats_death`  GROUP BY player ORDER BY SUM(amount) DESC LIMIT 1

I didn't have them like that at the same time. I first changed the controller, then when that didn't work I put it in the view. They both produce the same error. It is occurring on the line that I changed, so it's nothing wrong with the other code.

wherever possible It is desirable to use ruby methods rather than direct SQL statements as Ruby has the arrogance (rightly so) to believe it will do a better job of :-

1) Optimising the query to be run as efficiently as possible.

2) Making sure that the SQL that is actually run is as close as possible to the correct syntax for the database used according the the adapter specified in the database.yml for the current environent (Development, Test, Production)

The idea is that this enables the developer to concentrate more on getting the desired results and less on worrying about the intended database that is going to be used.
It doesn't always work that way, but 99.9% of the time ruby is very very good at achieving both those objectives.

So given my solution that returns an ActiveRecord::Relation do you get the desired result when you tag on a .first? If not what is the error?

SELECT player,SUM(amount) FROM Stats_death GROUP BY player ORDER BY SUM(amount) desc LIMIT 1

I was able to get what you posted to work, but it is returning an ActiveRecord::Relation. It was before returning the classname followed by the data. I used to get the following back

#<Statskill player: "specialrose">]

now I am getting this

ActiveRecord::Relation:0x007fd5be189200&gt

Here is how I modified your code to work.

Statsdeath.select("player SUM(amount)").group('player').order("SUM(amount) DESC")

Putting a .first on the end results in an error, and using .limit(1) returns the relation, so nothing changed.

Take the following data set as an example

1  specialrose  Skeleton  3  1
2  specialrose  Creeper  5  1
3  b                     Skeleton  3  1
4  b                     Creeper  2  1
5  b                     Bee  2  1
6  b                     Dragon  2  1

In the above example specialrose has died 8 times an b has died 9 times. Your SQL would pull out special rose as being the person that has died the most which is clearly not the case (I think!!!! )

you actually need a bit of complex logic involving grouping and summing to get the data you need which could be quite computationally expensive.
Possibly this might work for you

StatsDeath.group(['player', 'amount']).order('sum(amount DESC'))

I think (untested) the above should work for you and you should just be able to tag on a .first if you just want the players name.

Make that into a method on the StatsDeath model (def most_died_player or something like) which will make it simpler to test and easier to refactor if you need.

I URGE you to write a unit test for this using similar data to the above.

counter(int)|player(varchar)|cause(varchar)|amount(int)|entity(int).

The cause of death is the name of the mob, the entity is a boolean variable, 1 or 0, to signify that the cause of death was by a mob. I am trying to find the player who has died the most amount of times. It is as you said, and I am sorry for not including this, a person can be listed more than once. It seems they are listed once for each type of entity. For example

The top 2 rows of the db

1  specialrose  Skeleton  3  1
2  specialrose  Creeper  5  1

The code I listed works when I execute it in PHPMyAdmin, but it does not return the proper result set when I execute it in rails using Model.find_by_sql

Given the above your SQL statement is ambiguous as to it's intention is possibly an indication of why you, and I am struggling to get you to the right solution.

Think about it, and see if you can put into English the actual result you are looking for rather than trying to figure out an ambiguous SQL statement conversion.

Order the data first, or use the group_by function, whichever best suits your needs.

Normally in a relational database you would have a players table linked to a stats_death table in a one to many relationship. If you had this then the approach would be slightly different, you would find the correct player record then sum the amount column for all the stats_death records fro that player which would look something like this
Player.find(some_id).stat_deaths.sum(:amount)

SELECT player,SUM(amount) FROM Stats_death ORDER BY amount desc LIMIT 1

Any help is appreciated.