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:

-- This SQL query retrieves various salary statistics for employees in department '90', grouped by job title.

SELECT job_id, -- Selects the job_id column
SUM(salary), -- Calculates the total salary for each job title in department '90'
AVG(salary), -- Calculates the average salary for each job title in department '90'
MAX(salary), -- Finds the maximum salary for each job title in department '90'
MIN(salary) -- Finds the minimum salary for each job title in department '90'
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE department_id = '90' -- Filters the rows to include only those where department_id is '90'
GROUP BY job_id; -- Groups the results by job_id, so that the statistics are calculated for each unique job_id

Explanation:

• The SQL query calculates various salary statistics (total, average, maximum, and minimum) for employees in department '90', grouped by job title.
• The SELECT statement selects the job_id column along with the calculated statistics for each job title.
• The SUM(salary) function calculates the total salary for each job title within department '90'.
• The AVG(salary) function calculates the average salary for each job title within department '90'.
• The MAX(salary) function finds the maximum salary for each job title within department '90'.
• The MIN(salary) function finds the minimum salary for each job title within department '90'.
• The FROM clause specifies the table from which to retrieve the data, which is the employees table.
• The WHERE clause filters the rows to include only those where department_id is '90', ensuring that only employees from department '90' are considered.
• The GROUP BY clause groups the results by job_id, so that the statistics are calculated for each unique job title within department '90'.
• The result set will contain one row for each unique job_id, along with the total, average, maximum, and minimum salary for each job title within department '90'.

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.