Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (
    id int,
    name text,
    amount int,
    ratio int
);

INSERT INTO test VALUES
(2, 'a', 3, 1),
(3, 'a', 8, 2),
(4, 'a', 3, 31),
(5, 'a', 2, 10),
(6, 'a', 4, 15),
(7, 'b', 2, 42),
(8, 'b', 1, 42);

SELECT * FROM test;


id
name
amount
ratio


2
a
3
1

3
a
8
2

4
a
3
31

5
a
2
10

6
a
4
15

7
b
2
42

8
b
1
42



SELECT 7


SELECT DISTINCT ON (name)
    id, 
    name, 
    amount,
    SUM(ratio) OVER (PARTITION BY name)
FROM test
ORDER BY name, amount DESC


id
name
amount
sum


3
a
8
59

7
b
2
84



SELECT 2


SELECT 
    id, 
    name, 
    max_amount, 
    sum_ratio 
FROM (
    SELECT 
        t.*,
        MAX(amount) OVER w as max_amount,
        SUM(ratio) OVER w as sum_ratio
    FROM test t
    WINDOW w as (PARTITION BY name)
    ORDER BY name
) s 
WHERE amount = max_amount


id
name
max_amount
sum_ratio


3
a
8
59

7
b
2
84



SELECT 2