What are the six aggregate functions of SQL?

Aggregate functions that calculate summary values, such as averages and sums, from the values in a particular column and return a single value for each set of rows to which the function applies. The aggregate functions are AVG, COUNT, COUNT(*), MAX, MIN and SUM. Aggregate functions can be applied either to all rows in a table, to a subset of table rows specified by a WHERE clause, or to one or more groups of table rows specified by the GROUP BY clause.

Consider the employee(empno, ename, job, hiredate, sal, comm,deptno) for the example queries below.

1. COUNT ()

This function returns the number of data values of the specified column, for those rows that satisfy the condition of the WHERE clause. If the WHERE clause is omitted, then the query returns the total number of data values in the specified column.

For Example: The following query would return the number of Employees drawing a salary of more than 10000

SELECT COUNT (empno) FROM employee
WHERE salary > 10000;

2. COUNT (*)

This function returns the numbers of rows in the table rather than the number of data values in a column of the table.

For Example: The following query would return the number of employees

SELECT COUNT (*) FROM employees

3. MAX()

This function returns the maximum value of the specified column, for those rows that satisfy the condition of the WHERE clause. If the WHERE clause is omitted, then the query returns the maximum of all rows of the specified column.

For example: The following query would return the maximum salary drawn by an employee,

SELECT MAX (salary) FROM employee;

4. MIN()

This function returns the minimum value of the specified column, for those rows that satisfy the condition of the WHERE clause. If the WHERE clause is omitted, then the query returns the maximum of all rows of the specified column.

For example: The following query would return the maximum salary drawn by an employee,

SELECT MIN (salary) FROM employee;

5. AVG()

This function computes and returns the average of the data values of the specified numeric column, for those rows that satisfy the condition of the WHERE clause. If the WHERE clause is omitted, then the query returns the average of all data values of the specified column.

For example, the following query returns the average salary of all employees,

SELECT AVG (salary) FROM employee;

6. SUM()

This function computes and returns the sum of the data values of specified numeric column, for those rows that satisfy the condition of the WHERE clause. If the WHERE clause is omitted, then the query returns the average of all data values of the specified column.

For example, the following query returns the sum of salaries of all employees,

SELECT SUM (salary) FROM employee;