0

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

0
1

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

13
  • i assume you use mysql 8 and oracle have a row_number function
    – nbk
    Aug 26 '20 at 11:51
  • Both works for my database. Also in addition I will like to know the count(That is how many times the users purchase). Aug 26 '20 at 11:52
  • Your new updated code throws an error: Static analysis: 17 errors were found during analysis. An alias was previously found. (near "id" at position 70) Unexpected token. (near "id" at position 70) Unexpected token. (near "," at position 74) Unexpected token. (near "name" at position 76) Unexpected token. (near "," at position 82) Unrecognized keyword. (near "SUM" at position 84) Unexpected token. (near "(" at position 87) Aug 26 '20 at 11:57
  • @yodeveloper so not Oracle then? Aug 26 '20 at 20:53
  • The "Static analysis" phrasing suggests MySQL (I'd never seen it before either), so the Oracle tag seems to have been a mistake. Aug 26 '20 at 22:54

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.