I have two classes, User and Sell. I am trying to compose a request to find 10 people with the largest sum of sales

Task: Find 10 users

1. with the highest sum of product sales
2. find the largest count of sales

select sum_amount_total.*
from (select sum(amount) as total_amount
    from
        User as u
            join Sell as a on u.id = a.seller
    group by amount
) as amountSum
    join (select sum(amount) as sum_amount
    from
        Sell
    group by
        seller
    order by
        sum_amount desc
) as sum_amount_total on amountSum.total_amount > sum_amount_total.sum_amount limit 2;

create table script

Create table User (id int, name varchar(255));
Truncate table User;
insert into User (id, name) values ('1', 'Joe');
insert into User (id, name) values ('2', 'Henry');

Create table Sell (id int, seller INT, buyer INT, amount FLOAT);
Truncate table Sell;
insert into Sell (id, seller, buyer, amount) values (1, 1, 2, 134444.7);
insert into Sell (id, seller, buyer, amount) values (2, 2, 1, 1241.7);
insert into Sell (id, seller, buyer, amount) values (3, 1, 2, 123.4);
insert into Sell (id, seller, buyer, amount) values (4, 1, 2, 1000000.0);

Problem:

1. how to simplify the sql script?
2. I do not understand why my request is not working correctly. Example:

select * from Sell;

Result:

id|seller|buyer|amount
1, 1,     2,    134445
2, 1,     3,    1241.7
3, 2,     1,    123.4
4, 3,     3,    123.4
5, 3,     2,    134445
6, 2,     3,    1241.7
7, 2,     1,    123.4
8, 1,     3,    123.4
9, 1,     3,    10000
10,1,     3,    1000000

my query(1) for finding the largest amount:

sum_amount
134568.10312652588
134568.10312652588
1488.4999542236328
1488.4999542236328
1488.4999542236328
1488.4999542236328

My request does not correctly calculate the largest sum What needs to be changed for the sql script to work and add a condition to search for the largest count of sales? Thanks in advance for your answer!

CodePudding user response：

Simplifying your sql script for table User :

insert into User (id, name) values ('1', 'Joe'), ('2', 'Henry');

instead of

insert into User (id, name) values ('1', 'Joe');
insert into User (id, name) values ('2', 'Henry');

The same for table Sell :

insert into Sell (id, seller, buyer, amount) values (1, 1, 2, 134444.7), (2, 2, 1, 1241.7), (3, 1, 2, 123.4), (4, 1, 2, 1000000.0);

Then finding 10 users with the highest sum of product sales :

WITH list AS
(
select seller, sum(amount) as total_amount
  from Sell
 group by seller
 order by total_amount DESC
 limit 10
)
select u.*, l.total_amount
  from list AS l
 inner join user AS u
    on u.id = l.seller

Finally finding 10 users with the largest count of product sales :

WITH list AS
(
select seller, coun(*) as total_count
  from Sell
 group by seller
 order by total_count DESC
 limit 10
)
select u.*, l.total_count
  from list AS l
 inner join user AS u
    on u.id = l.seller

CodePudding user response：

create table script

Create table User(id int, name varchar(255));
Truncate table Users;
insert into Users (id, name) values ('1', 'Joe');
insert into Users (id, name) values ('2', 'Henry');
insert into Users (id, name) values ('3', 'Liza');

Create table Sell (id int, seller INT, buyer INT, amount FLOAT);
Truncate table Sell;
insert into Sell (id, seller, buyer, amount) values (1, 1, 2, 134444.7);
insert into Sell (id, seller, buyer, amount) values (2, 2, 1, 1241.7);
insert into Sell (id, seller, buyer, amount) values (3, 1, 2, 123.4);
insert into Sell (id, seller, buyer, amount) values (4, 1, 2, 1000000.0);
insert into Sell (id, seller, buyer, amount) values (5, 3,     2,    134445);
insert into Sell (id, seller, buyer, amount) values (6, 2,     3,    1241.7);
insert into Sell (id, seller, buyer, amount) values (7, 2,     1,    123.4);
insert into Sell (id, seller, buyer, amount) values (8, 3,     3,    123.4);
insert into Sell (id, seller, buyer, amount) values (9, 1,     3,    10000);
insert into Sell (id, seller, buyer, amount) values (10,1,     3,    1000000);

query to get count of sales

SELECT name,count(Sell.id) as nsales,sum(amount),rank() 
       over(order by count(Sell.id) desc) 
   from Sell 
       inner join Users 
   on(seller=Users.id) group by name
   limit 10;

name  | nsales |    sum    | rank
------- -------- ----------- ------
 Joe   |      5 | 2144568.1 |    1
 Henry |      3 |    2606.8 |    2
 Liza  |      2 |  134568.4 |    3

and query to get top of amount

SELECT name,count(Sell.id) as nsales,sum(amount),rank()
       over(order by sum(amount) desc) 
    from Sell 
       inner join Users 
    on(seller=Users.id) group by name 
    limit 10;

 name  | nsales |    sum    | rank
------- -------- ----------- ------
 Joe   |      5 | 2144568.1 |    1
 Liza  |      2 |  134568.4 |    2
 Henry |      3 |    2606.8 |    3

same result without ranking

SELECT name,count(Sell.id) as nsales,sum(amount) amount_sum 
    from Sell inner join Users on(seller=Users.id) 
group by name 
order by amount_sum desc
limit 10;

 name  | nsales | amount_sum
------- -------- ------------
 Joe   |      5 |  2144568.1
 Liza  |      2 |   134568.4
 Henry |      3 |     2606.8

SELECT name,count(Sell.id) as nsales,sum(amount) amount_sum 
    from Sell inner join Users on(seller=Users.id) 
group by name 
order by nsales desc 
limit 10;

 name  | nsales | amount_sum
------- -------- ------------
 Joe   |      5 |  2144568.1
 Henry |      3 |     2606.8
 Liza  |      2 |   134568.4