Mysql – Users with highest sum up price

MySQLquery

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 "price" 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).

Best Answer

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<>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<>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<>fiddle here

Related Question