PostgreSQL Aggregate Functions and Group By: Get the total salary, maximum, minimum and average salary of all posts for a particular department

12. Write a query to get the total salary, maximum, minimum and average salary of all posts for those departments which ID 90.

Sample Solution:

Code:

SELECT job_id, SUM(salary), AVG(salary), MAX(salary), MIN(salary) 
FROM employees 
WHERE department_id = '90' 
GROUP BY job_id;

Sample table: employees

Output:

pg_exercises=# SELECT job_id, SUM(salary), AVG(salary), MAX(salary), MIN(salary)
pg_exercises-# FROM employees
pg_exercises-# WHERE department_id = '90'
pg_exercises-# GROUP BY job_id;
 job_id  |   sum    |        avg         |   max    |   min
---------+----------+--------------------+----------+----------
 AD_PRES | 24000.00 | 24000.000000000000 | 24000.00 | 24000.00
 AD_VP   | 34000.00 | 17000.000000000000 | 17000.00 | 17000.00
(2 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 average salary for each post excluding programmer.
Next: Write a query to get the job ID and maximum salary of each post for maximum salary is at or above $4000.