There are two tables - users and their purchases:

users id name statistics id sum user_id

It is necessary, in general, to select all users by the total amount for which they bought.
I make a request, for some reason it does not work:

 SELECT user.* FROM users, (SELECT SUM(statistics.sum) FROM statistics) as sum ORDER BY sum DESC
  • How do you relate these tables? - andreyqin
  • statistics id sum user_id - Ali
  • @Ali, If you are given a comprehensive answer, mark it as correct (click on the check mark next to the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

In general, your structure is incorrect. Nevertheless, try this:

 SELECT u.name, SUM(s.sum) AS sum FROM users u JOIN statistics s ON s.user_id = u.id GROUP BY u.name ORDER BY sum DESC

Update

It is better to do 3 tables:

 users: id | name products: id | name | price orders: id | user_id | product_id | quantity

And the request will look like this:

 SELECT u.name, SUM(p.price * o.quantity) AS sum FROM users u JOIN orders o ON o.user_id = u.id JOIN products p ON p.id = o.product_id GROUP BY u.name ORDER BY sum DESC
  • Thank! Earned)) In general, how best to make the structure? - Ali
  • Updated the answer. - andreyqin