1
\$\begingroup\$

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);
\$\endgroup\$
  • 1
    \$\begingroup\$ I appreciate you adding the DDL (create table) and data load (insert statements) in the question. This facilitates reviewing SQL alot! \$\endgroup\$ – dfhwze Jul 4 at 8:28
1
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.