MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (emp_id INT, quantity INT);
INSERT INTO test VALUES
(1001,   21),
(1001,   10),
(1002,   3),
(1002,   5),
(1004,   4),
(1004,   5),
(1004,   8);
SELECT * FROM test;


emp_id
quantity


1001
21

1001
10

1002
3

1002
5

1004
4

1004
5

1004
8


SELECT emp_id, SUM(quantity), DENSE_RANK() OVER (ORDER BY SUM(quantity) DESC)
FROM test
GROUP BY 1
ORDER BY 2 DESC;


emp_id
SUM(quantity)
DENSE_RANK() OVER (ORDER BY SUM(quantity) DESC)


1001
31
1

1004
17
2

1002
8
3


WITH cte AS ( SELECT emp_id, SUM(quantity) `sum`, DENSE_RANK() OVER (ORDER BY SUM(quantity) DESC) `rank`
              FROM test
              GROUP BY 1 )
SELECT emp_id, `sum`
FROM cte
WHERE `rank` = 2;


emp_id
sum


1004
17


INSERT INTO test VALUES
(1010, 100), (1020, 100);
SELECT emp_id, 
       SUM(quantity) `SUM`, 
       DENSE_RANK() OVER (ORDER BY SUM(quantity) DESC) `DENSE_RANK`, 
       RANK() OVER (ORDER BY SUM(quantity) DESC) `RANK`
FROM test
GROUP BY 1
ORDER BY 2 DESC, 1 DESC;


emp_id
SUM
DENSE_RANK
RANK


1020
100
1
1

1010
100
1
1

1001
31
2
3

1004
17
3
4

1002
8
4
5