Aggregate Function in SQL with example

Aggregate Function in SQL

SQL aggregates functions such as SUM, AVG, MAX, MIN, COUNT, etc. to produce a single value for the entire group of table entries. Aggregate functions can also be used along with the SELECT command.

SUM:

The SUM function calculates the arithmetic SUM of all selected values of a given column. For example, to find the SUM of BASIC salary from the EMPLOYEE table, we would enter the following command:

SELECT SUM(BASIC) FROM EMPLOYEE;

AVG:

The function AVG calculates the average of all selected values of a given field. For example, to find the average BASIC salary, enter the following command:

SELECT AVG(BASIC) FROM EMPLOYEE;

AVG(DISTINCT) eliminates duplicate field values before calculating the average.

MAX:

MAX function calculates the largest of all selected values of a given column. For example, using the EMPLOYEE table, the following command displays the maximum salary value in the column Basic:

SELECT MAX(BASIC) FROM EMPLOYEE;

MIN:

MIN function calculates the smallest of all selected values of a given column. For example, using the EMPLOYEE table, the following command displays the minimum salary value in the column Basic:

SELECT MIN(BASIC) FROM EMPLOYEE;

COUNT:

This function counts the number of rows in the output table. The function COUNT with the asterisk (*) counts the number of rows in the resulting table. COUNT(DISTINCT) eliminates rows containing duplicate field values before counting them in the output table. For example, to find how many employees are in the EMPLOYEE table, type the following query:

SELECT COUNT(*) FROM EMPLOYEE;