# AGGREGATE FUNCTION

(i) Aggregate functions perform a calculation on a set of values and return a single value.

(ii) Aggregate functions return the same value any time that they are called by using a specific set of input values.

(iii) Aggregate functions can be used as expressions only in the following:

(a) The select list of a SELECT (either a subquery or an outer query).

(b) A HAVING clause.

The AVG() Function returns the average value of a numeric column.

The COUNT() Function returns the number of rows that matches a specified criteria.

COUNT (column_name) syntax:

It returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT (column_name) FROM table_name;

COUNT ( * ) Syntax:

It returns the number of records in a table:

SELECT COUNT ( * ) FROM table_name;

COUNT ( DISTINCT column_name ) Syntax:

It returns the number of distinct values of the specified column:

SELECT COUNT ( DISTINCT column_name) FROM table_name;

The MAX() function returns the largest value of the selected column.

The MIN() Function returns the smallest value of the selected column.

The SUM() Function returns the total sum of a numeric column.

The LEN() Function returns the length of the value in a text field.

(ii) Aggregate functions return the same value any time that they are called by using a specific set of input values.

(iii) Aggregate functions can be used as expressions only in the following:

(a) The select list of a SELECT (either a subquery or an outer query).

(b) A HAVING clause.

**AVG() Function:**The AVG() Function returns the average value of a numeric column.

**Syntax:**SELECT AVG (column_name) FROM table_name**COUNT() Function:**The COUNT() Function returns the number of rows that matches a specified criteria.

COUNT (column_name) syntax:

It returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT (column_name) FROM table_name;

COUNT ( * ) Syntax:

It returns the number of records in a table:

SELECT COUNT ( * ) FROM table_name;

COUNT ( DISTINCT column_name ) Syntax:

It returns the number of distinct values of the specified column:

SELECT COUNT ( DISTINCT column_name) FROM table_name;

**MAX() Function:**The MAX() function returns the largest value of the selected column.

**Syntax:**SELECT MAX ( column_name ) FROM table_name;**MIN() Function:**The MIN() Function returns the smallest value of the selected column.

**Syntax:**SELECT MIN(column_name) FROM table_name;**SUM() Function:**The SUM() Function returns the total sum of a numeric column.

**Syntax:**SELECT SUM (column_name) FROM table_name;

**LEN() Function:**The LEN() Function returns the length of the value in a text field.

**Syntax:**SELECT LEN (column_name) FROM table_name;

**HAVING Clause:**The HAVING clause was added to SQL because the WHERE keyword could be used with aggregate functions.

**GROUP BY Statement:**The GROUP BY statement is used in conjunction with the aggregate functions to group the result_set by one or more columns.
## No comments