Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
  id INT,
  dept VARCHAR,
  value INT
);
INSERT INTO test (id, dept, value) VALUES (1, 'A', 5);
INSERT INTO test (id, dept, value) VALUES (1, 'A', 5);
INSERT INTO test (id, dept, value) VALUES (1, 'B', 5);
INSERT INTO test (id, dept, value) VALUES (1, 'C', 5);
INSERT INTO test (id, dept, value) VALUES (2, 'A', 5);
INSERT INTO test (id, dept, value) VALUES (2, 'A', 5);
INSERT INTO test (id, dept, value) VALUES (2, 'B', 15);
INSERT INTO test (id, dept, value) VALUES (2, 'A', 2);

SELECT * FROM test
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
dept
value


1
A
5

1
A
5

1
B
5

1
C
5

2
A
5

2
A
5

2
B
15

2
A
2


    SELECT
        id,
        dept,
        value,
        FIRST_VALUE(dept) OVER (PARTITION BY id ORDER BY value DESC) as highest_value_dept
    FROM test


id
dept
value
highest_value_dept


1
A
5
A

1
B
5
A

1
C
5
A

1
A
5
A

2
B
15
B

2
A
5
B

2
A
5
B

2
A
2
B


SELECT
    id,
    highest_value_dept,
    SUM(value) as value,
    MODE() WITHIN GROUP(ORDER BY dept) AS dept_freq
FROM (
    SELECT
        id,
        dept,
        value,
        FIRST_VALUE(dept) OVER (PARTITION BY id ORDER BY value DESC) as highest_value_dept
    FROM test
) s
GROUP BY 1,2


id
highest_value_dept
value
dept_freq


1
A
20
A

2
B
27
A