PostgreSQL – Aggregate Functions

Aggregate functions are functions that are computed over a set of rows to return an output. Average, Max, Min, Sum and Count are the aggregate functions.

Example:

Consider an employee table with 5 employees and their salaries as shown below:

Employee_NameSalary
emp150,000
emp255,000
emp360,000
emp465,000
emp570,000
table_name : employee

Query to return employee count:

SELECT count(employee_name) FROM employee;

Query to determine the average salary of all employees:

SELECT avg(salary) FROM employee;

Query to determine the maximum of salaries:

SELECT max(salary) FROM employee;

Query to determine the minimum of salaries:

SELECT min(salary) FROM employee;

Query to determine the sum of salaries of all employees:

SELECT sum(salary) FROM employee;

Query to identify the employee with highest salary:

SELECT employee_name FROM employee WHERE salary=(SELECT max(salary) FROM employee);

Query to identify all employees who are making less than the average salary:

SELECT employee_name FROM employee WHERE salary < (SELECT avg(salary) FROM employee);

See also  PostgreSQL - Change owner of all tables within a schema