counting and grouping mysql data

Question

say I have a table with data like this - a hypothetical racing game

at the end of each game, a user's name, their position in the game and the winnings they earned are added to the table

 *****************************************
 | name  | place | date       | winnings |
 | bob   | 1     | 2015-07-08 | 100      |
 | bob   | 2     | 2015-07-07 | 75       |
 | john  | 1     | 2015-07-07 | 100      |
 | john  | 1     | 2015-07-04 | 100      |
 | sarah | 9     | 2015-07-05 | 1000     |
 *****************************************

I would use the following query to get this

 select h.name, z.place, z.date, z.winnings from races as z, users as h where h.id  = z.user_id order by date desc 

my question is

how would I get it to output something like this? (add the winnings together and sort with them)

 **************************
 | name  | total_winnings |
 | sarah | 1000           |
 | john  | 200            |
 | bob   | 175            |
 **************************

as well as how sorted by how many times they won (were in first place)

 ***************************
 | name  | won_first_place |
 | john  | 2               |
 | bob   | 1               |
 | sarah | 0               | 
 ***************************

Thanks

Answer 1

You can handle both of those queries similarly, because both will require grouping by user.

SELECT 
    users.name, 
    SUM(races.winnings) AS total_winnings
FROM users INNER JOIN races ON users.id  = races.user_id
GROUP BY users.id
ORDER BY SUM(races.winnings) DESC

should get you the total winnings, and

SELECT 
    users.name, 
    SUM(CASE WHEN races.place=1 THEN 1 ELSE 0 END) AS won_first_place
FROM users INNER JOIN races ON users.id  = races.user_id
GROUP BY users.id
ORDER BY SUM(CASE WHEN races.place=1 THEN 1 ELSE 0 END) DESC

should get you the number of times the user got first place.

When you use GROUP BY you can use many different aggregate functions such as SUM on columns in the grouped rows. You can check out the MySQL documentation for other options.