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.

Sample Solution:

Code:

SELECT job_id, MAX(salary)  
FROM employees 
GROUP BY job_id 
HAVING MAX(salary) >=4000;

Sample table: employees

Output:

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
------------+----------
 AC_ACCOUNT |  8300.00
 ST_MAN		|  8200.00
 IT_PROG    |  9000.00
 SA_MAN     | 14000.00
 AD_PRES    | 24000.00
 AC_MGR     | 12000.00
 FI_MGR     | 12000.00
 AD_ASST    |  4400.00
 MK_MAN     | 13000.00
 HR_REP     |  6500.00
 PR_REP     | 10000.00
 FI_ACCOUNT |  9000.00
 SH_CLERK   |  4200.00
 AD_VP      | 17000.00
 SA_REP     | 11500.00
 MK_REP     |  6000.00
 PU_MAN     | 11000.00
(17 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

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.