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

Last update on February 07 2026 12:18:28 (UTC/GMT +8 hours)

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

 +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   | EMAIL    | PHONE_NUMBER       | HIRE_DATE  | JOB_ID     | SALARY   | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven      | King        | SKING    | 515.123.4567       | 1987-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |   		  90 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 1987-06-18 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 1987-06-19 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 1987-06-20 | IT_PROG    |  9000.00 |           0.00 |        102 |            60 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 1987-06-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 1987-06-22 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 1987-06-23 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 1987-06-24 | IT_PROG    |  4200.00 |           0.00 |        103 |            60 |
|         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 1987-06-25 | FI_MGR     | 12000.00 |           0.00 |        101 |           100 |
..........
|         206 | William     | Gietz       | WGIETZ   | 515.123.8181       | 1987-10-01 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110 |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

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:

Go to:

PREV : 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.

Practice Online

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