MySQL GROUP BY id, then show name I have a users table where some users have the same name:

+----+------+   
| id | name |                                                         
+----+------+              
|  1 | Joe  |              
|  2 | Joey |              
|  3 | Joe  |
+----+------+


... and a purchases table:

+---------+--------+
| user_id | amount |
+---------+--------+
|       1 |    400 |
|       1 |    200 |
|       2 |    700 |
|       3 |    100 |
+---------+--------+


Goal

I want to show for each user, their name and the sum of their purchases like this (yes there are two people named Joe, no worry):

+------+-------+
| name | total |
+------+-------+
| Joe  |   600 |
| Joey |   700 |
| Joe  |   100 |
+------+-------+


My code

SELECT name, totals.total
FROM
  (
    SELECT user_id, SUM(amount) total
    FROM purchases
    GROUP BY user_id
  ) AS totals
INNER JOIN users u
ON totals.user_id=u.id;


It works, producing the right output.

How can I improve this code, in particular by not using a subquery?

Table creation code:

CREATE TABLE users (id INT UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));
INSERT INTO users (name) VALUES ('Joe'), ('Joey'), ('Joe');
CREATE TABLE purchases (user_id INT NOT NULL, amount INT);
INSERT INTO purchases (user_id, amount) VALUES (1, 400), (1, 200), (2, 700), (3, 100);
If you don't want to use a subquery, you can include the second table with a join before the group by:
SQL Fiddle

SELECT max(name) name, SUM(amount) total
FROM purchases
INNER JOIN users on users.id = purchases.user_id
GROUP BY user_id
ORDER BY user_id


yielding

name    total
Joe       600
Joey      700
Joe       100