w3resource

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
------------+----------
 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)

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming