Users with highest sum up price I have a table with name, email, user_id, and price. On this table, a user can appear more than once depending on how many purchases. I am trying to get select all details of users with the highest sum of price.
id   name  price   email    user_id
--   ----  ----- ---------  -------
1    Mike  100   @mike.com      5
2    John  50    @john.com      6
3    Mike  100   @mike.com      5
4    John  50    @john.com      1
5    Mark  50    @mark.com      3 
6    Mike  100   @mike.com      5


As you can see from above Mike has the highest pricing in total I want a query that will get the sum total of column &quot;price&quot; and select all details of the row with the highest total first in an array and follow by the next higher. The result should be like below:
id   name  price   email    user_id
--   ----  ----- ---------  -------
1    Mike  300   @mike.com      5
2    John  100   @john.com      6
3    Mark  50    @mark.com      3

I want the 'id' to reset and start listing 1,2,3,4,5... and also I have other columns on the table I will like to get.
Also in addition I will like to know the count(how many times the users purchase).
Basically you want this.
The problem is that john has 2 user_ids 1 and 6
Else i would put the user_id also in the GROUP BY
AND if you only the highest 3 You have to add also LIMIT 3
A MySQL 5.x Solution to your problem


CREATE TABLE sumprice (
  `id` INTEGER,
  `name` VARCHAR(4),
  `price` INTEGER,
  `email` VARCHAR(9),
  `user_id` INTEGER
);

INSERT INTO sumprice
  (`id`, `name`, `price`, `email`, `user_id`)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5'),
  ('2', 'John', '50', '@john.com', '6'),
  ('3', 'Mike', '100', '@mike.com', '5'),
  ('4', 'John', '50', '@john.com', '1'),
  ('5', 'Mark', '50', '@mark.com', '3'),
  ('6', 'Mike', '100', '@mike.com', '5');




SELECT   @id:= @id +1  `id`, `name`, SUM(`price`) price , `email`, MAX(`user_id`)
FROM sumprice,(SELECT @id:=0) a
GROUP BY `email`, `name`
ORDER BY price DESC


id | name | price | email     | MAX(`user_id`)
-: | :--- | ----: | :-------- | -------------:
 1 | Mike |   300 | @mike.com |              5
 2 | John |   100 | @john.com |              6
 3 | Mark |    50 | @mark.com |              3


db&lt;&gt;fiddle here
In Mysql 8


CREATE TABLE sumprice (
  `id` INTEGER,
  `name` VARCHAR(4),
  `price` INTEGER,
  `email` VARCHAR(9),
  `user_id` INTEGER
);

INSERT INTO sumprice
  (`id`, `name`, `price`, `email`, `user_id`)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5'),
  ('2', 'John', '50', '@john.com', '6'),
  ('3', 'Mike', '100', '@mike.com', '5'),
  ('4', 'John', '50', '@john.com', '1'),
  ('5', 'Mark', '50', '@mark.com', '3'),
  ('6', 'Mike', '100', '@mike.com', '5');




SELECT   ROW_NUMBER() OVER(
        ORDER BY SUM(price) DESC
    )  `id`, `name`, SUM(`price`) price , `email`, MAX(`user_id`)
FROM sumprice
GROUP BY `email`, `name`
ORDER BY price DESC


id | name | price | email     | MAX(`user_id`)
-: | :--- | ----: | :-------- | -------------:
 1 | Mike |   300 | @mike.com |              5
 2 | John |   100 | @john.com |              6
 3 | Mark |    50 | @mark.com |              3


db&lt;&gt;fiddle here
In Oracle it is the same


CREATE TABLE sumprice (
  id NUMBER(5),
  name VARCHAR(4),
  price NUMBER(5),
  email VARCHAR(9),
  user_id NUMBER(5)
);




INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5');




INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES('2', 'John', '50', '@john.com', '6');




INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('3', 'Mike', '100', '@mike.com', '5');




INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('4', 'John', '50', '@john.com', '1');




INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('5', 'Mark', '50', '@mark.com', '3');




INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('6', 'Mike', '100', '@mike.com', '5');




SELECT     ROW_NUMBER() OVER(
        ORDER BY SUM(price) DESC
    ) id ,name, SUM(price) price , email, MAX(user_id)
FROM sumprice
GROUP BY email, name
ORDER BY price DESC


ID | NAME | PRICE | EMAIL     | MAX(USER_ID)
-: | :--- | ----: | :-------- | -----------:
 1 | Mike |   300 | @mike.com |            5
 2 | John |   100 | @john.com |            6
 3 | Mark |    50 | @mark.com |            3


db&lt;&gt;fiddle here