SQL join and sum of items

Question

There are two tables

users

+--+----+
|id|name|
+--+----+
 1   A    
 2   B

orders

+--+--------+-------+-------+
|id|subtotal|created|user_id|
+--+--------+-------+-------+
 1  10       1000001   1
 2  20       1000002   1
 3  10       1000003   2
 4  10       1000005   1

The idea is to get AVG, SUM and the last created order from the users.

SELECT
  users.name,
  users.phone,
  SUM(a.subtotal),
  COALESCE(a.created, NULL)
  FROM users
LEFT JOIN
  (
  SELECT
    orders.id,
    orders.subtotal,
    orders.user_id,
    orders.created
  FROM
    orders 
  JOIN(
    SELECT MAX(i.created) created, i.user_id 
      FROM orders i
      GROUP BY i.user_id
  )AS j ON(j.user_id = orders.user_id AND orders.created = j.created) GROUP BY orders.user_id
) AS a ON users.id = a.user_id
GROUP BY users.id

For example the SQL request should return this:

+--+----+---+--------+
|id|name|sum|date    |
+--+----+---+--------+
 1   A    40  1000005
 2   B    10  1000003

But the SQL above failed to calculate sum. What did i miss?

Answer 1

Your query seems way too complicated. How about this?

SELECT u.id, u.name, SUM(o.subtotal), MAX(o.created)
FROM users u LEFT JOIN
     orders o
     ON u.id = o.user_id
GROUP BY u.id, u.name;

In MySQL it is particularly important to avoid unnecessary subqueries in the FROM clause. These are actually materialized and that can impede the use of indexes for performance.