PostgreSQL Aggregate Functions and Group By: Get the highest, lowest, total, and average salary of all employees

6. Write a query to get the highest, lowest, total, and average salary of all employees.

Sample Solution:

Code:

-- This SQL query calculates summary statistics of salary from the employees table.

SELECT 
ROUND(MAX(salary),0) "Maximum", -- Selects the maximum salary and rounds it to the nearest whole number
ROUND(MIN(salary),0) "Minimum", -- Selects the minimum salary and rounds it to the nearest whole number
ROUND(SUM(salary),0) "Sum", -- Calculates the sum of all salaries and rounds it to the nearest whole number
ROUND(AVG(salary),0) "Average" -- Calculates the average salary and rounds it to the nearest whole number
FROM employees; -- Specifies the source table "employees" from which data is retrieved

Explanation:

• The SQL query retrieves summary statistics (maximum, minimum, sum, and average) of the salary column from the employees table.
• Each statistical measure is rounded to the nearest whole number for readability.
• The MAX() function finds the highest salary.
• The MIN() function finds the lowest salary.
• The SUM() function calculates the total sum of all salaries.
• The AVG() function computes the average salary.

Sample table: employees

Output:

pg_exercises=# SELECT ROUND(MAX(salary),0) "Maximum",
pg_exercises-# ROUND(MIN(salary),0) "Minimum",
pg_exercises-# ROUND(SUM(salary),0) "Sum",
pg_exercises-# ROUND(AVG(salary),0) "Average"
pg_exercises-# FROM employees;
 Maximum | Minimum |  Sum   | Average
---------+---------+--------+---------
   24000 |    2100 | 691400 |    6462
(1 row)

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 and number of employees working in the department which ID is 90.
Next: Write a query to get the number of employees working in each post.