PostgreSQL Aggregate Functions and Group By: Get the maximum salary of each post which is at or above a specific amount
13. Write a query to get the job ID and maximum salary of each post for maximum salary is at or above $4000.
SELECT job_id, MAX(salary) FROM employees GROUP BY job_id HAVING MAX(salary) >=4000;
Sample table: employees
pg_exercises=# SELECT job_id, MAX(salary) pg_exercises-# FROM employees pg_exercises-# GROUP BY job_id pg_exercises-# HAVING MAX(salary) >=4000; job_id | max ------------+---------- Salesman | 8230.00 AC_ACCOUNT | 8330.00 IT_PROG | 9030.00 SA_MAN | 14030.00 AD_PRES | 24030.00 AC_MGR | 12030.00 FI_MGR | 12030.00 AD_ASST | 4430.00 MK_MAN | 13030.00 HR_REP | 6530.00 PR_REP | 10030.00 FI_ACCOUNT | 9030.00 SH_CLERK | 4230.00 AD_VP | 17030.00 SA_REP | 11530.00 MK_REP | 6030.00 PU_MAN | 11030.00 (17 rows)
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query to get the total salary, maximum, minimum and average salary of all posts for those departments which ID 90.
Next: Write a query to get the average salary for all departments working more than 10 employees.
What is the difficulty level of this exercise?