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:

-- This SQL query retrieves the maximum salary for each job title where the maximum salary is greater than or equal to 4000.

SELECT job_id, -- Selects the job_id column
MAX(salary) -- Finds the maximum salary for each job title
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
GROUP BY job_id -- Groups the results by job_id, so that the maximum salary is calculated for each unique job_id
HAVING MAX(salary) >= 4000; -- Filters the grouped results to include only those where the maximum salary is greater than or equal to 4000

Explanation:

• The SQL query retrieves the maximum salary for each job title where the maximum salary is greater than or equal to 4000.
• The SELECT statement selects the job_id column along with the maximum salary for each job title.
• The MAX(salary) function finds the maximum salary for each job title.
• The FROM clause specifies the table from which to retrieve the data, which is the employees table.
• The GROUP BY clause groups the results by job_id, ensuring that the maximum salary is calculated for each unique job title.
• The HAVING clause filters the grouped results to include only those where the maximum salary is greater than or equal to 4000.
• The result set will contain one row for each unique job_id where the maximum salary is greater than or equal to 4000, along with the corresponding maximum salary.

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.