Convert MySQl query into Rails

There's not enough information to answer your question.
What tables do you have and what are there relationships? How have you represented those relationships in your models?
You would typically write these as two separate scopes and chain them together when you need them using .order, .sum and the various finders.

O.K., The fact that you have an amount column that you are both summing and ordering by is somewhat confusing. It implies that there would be more than one stats_death record for a player and that each record may contain an amount > 1

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)

Now things are clearer.
The SQL you have doesn't really work, it just looks like it works as it doesn't take care of a player appearing more than once and you are wanting to find the player that has died the most amount of times regardless of any other factors.

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.

ActiveRecord::Relation (AKA ARel) is the desired result, it's all about lazy loading which enhances the ability seamlessly to do things like fragment caching, tagging an enumerable, such as .first, on to the end of an ARel result should then cause the actual query to be run.
The idea is that ruby is preparing the query to be run before it is actually needed. Using the ARel should then cause the SQL to be run.

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?