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_Name | Salary |
emp1 | 50,000 |
emp2 | 55,000 |
emp3 | 60,000 |
emp4 | 65,000 |
emp5 | 70,000 |
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);