CODEWITHSUNDEEP
postgresql
lesliebooks
lesliebooks

Reputation: 37

How to find the average salary in each department and the top 2 salaried employees in each department?

Find the average salary in each department and the top 2 salaried employees in each department.

table code:

CREATE TABLE employees (
    eid integer,
    ename text,
    dept double precision,
    salary double precision
);

INSERT INTO employees (eid, ename, dept, salary) VALUES (11124, 'Gladys 
Porenta', 1990, 17037);
INSERT INTO employees (eid, ename, dept, salary) VALUES (11881, 'Arra Staelin', 
1990, 72328);
INSERT INTO employees (eid, ename, dept, salary) VALUES (12108, 'Akeel Frezza', 
1990, 54679);
INSERT INTO employees (eid, ename, dept, salary) VALUES (16556, 'Mostafa 
Hiltgen', 1991, 74610);
INSERT INTO employees (eid, ename, dept, salary) VALUES (16859, 'Chikara 
Heping', 1991, 10142);
INSERT INTO employees (eid, ename, dept, salary) VALUES (17236, 'Matk Merro', 
1991, 63657);
INSERT INTO employees (eid, ename, dept, salary) VALUES (18404, 'Lein Feldmann', 
1992, 44999);
INSERT INTO employees (eid, ename, dept, salary) VALUES (21473, 'Youssef 
Calkin', 1992, 91967);
INSERT INTO employees (eid, ename, dept, salary) VALUES (21669, 'Morrie 
Servieres', 1992, 88630);

I have written to find the top 2 salaried employees in each department...

select * from (
    select *, rank() over (partition by dept order by salary desc) emp_rank
    from employees) as t1
where emp_rank <= 2;

enter image description here

But how do I add another column that shows the average salary for each department?

For example, next to the emp_rank column it should show and avg_salary column that reads 48014.67 for dept 1990, 49469.67 for dept 1991, and 75198.67 for dept 1992.

Thank you for your help!

Upvotes: 1

Views: 76

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

Just like rank() window function you have used, use avg(salary) window function. You will get your desired output. Try this.

select * from (
    select *,
    rank() over (partition by dept order by salary desc) emp_rank,
    avg(salary) over (partition by dept) avg_salary
    from employees) as t1
where emp_rank <= 2;

Demo

Upvotes: 1

Related Questions