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.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics