Aggregate or Group Functions in SQL

These functions return a single row based on group of rows. These can appear in SELECT list and HAVING clauses. These functions operate on sets of rows to give one result per group. The sets may be the whole table or the table split into groups.

Guidelines to use Group Functions:

1. DISTINCT keyword makes the function consider only non-duplicate values.
2. ALL keyword makes the function to consider every value including duplicates.
Syntax: GROUP_FUNCTION_NAME(DISTINCT / ALL / n)
3. The data type for arguments may CHAR, VARCHAR, NUMBER or DATE.
4. All group functions except COUNT (*) ignore NULL values. The NVL function is used to substitute a value for NULL values.
5. If a GROUP function declared in a SELECT statement, no single row columns should be declared.
6. When a group function is declared in a SELECT list, other columns can be declared. But, they should be grouped columns and all the non-functional columns.

Average Function:
As per the below syntax, it returns the average value of n and it ignores NULL values.

Syntax: AVG(DISTINCT / ALL / n)
Examples:

SQL> SELECT AVG(SAL),AVG(DISTINCT SAL) FROM EMP;
SQL> SELECT AVG(COMM),AVG(DISTINCT AVG) FROM EMP;

SUM Function:
As per the below syntax, it returns the SUM of the values of n and it ignores NULL values.

Syntax: SUM(DISTINCT / ALL / n)
Examples:

SQL> SELECT SUM(SAL),SUM(DISTINCT SAL) FROM EMP;
SQL> SELECT SUM(COMM),SUM(DISTINCT AVG) FROM EMP;

MAX (Maximum) Function:
As per the below syntax, it returns the maximum value of n and it ignores NULL values.

Syntax: MAX(DISTINCT / ALL / n)
Examples:

SQL> SELECT MAX(SAL), MAX(DISTINCT SAL) FROM EMP;
SQL> SELECT MAX(COMM), MAX(DISTINCT COMM) FROM EMP;

MIN (Minimum) Function:
As per the below syntax, it returns the minimum value of the n and it ignores NULL values.

Syntax: MIN(DISTINCT / ALL /n)
Examples:

SQL> SELECT MIN(SAL), MIN(DISTINCT SAL) FROM EMP;
SQL> SELECT MIN(COMM), MIN(DISTINCT COMM) FROM EMP;

Standard Deviation Function (STDDEV):
As per the below syntax, it returns the standard deviation value of n and it ignores the NULL values.

Syntax: STDDEV(DISTINCT / ALL / n)
Examples:

SQL> SELECT STDDEV(SAL), STDDEV(DISTINCT SAL) FROM EMP;
SQL> SELECT STDDEV(COMM), STDDEV(DISTINCT COMM) FROM EMP;

Variance Function:
As per the below syntax, it returns the variance of n and it ignores the NULL values.

Syntax: VARIANCE(DISTINCT / ALL / n)
Examples:

SQL> SELECT VARIANCE(SAL), VARIANCE(DISTINCT SAL) FROM EMP;
SQL> SELECT VARIANCE(COMM), VARIANCE(DISTINCT COMM) FROM EMP;

COUNT Function:
As per the below syntax, it returns the number of rows in the query. N evaluates to something other than NULL. It is used to return all rows, including duplicates and NULL values. This function can be used to specify the count of all rows or only distinct values of n.

Syntax: COUNT(* / DISTINCT / ALL / n)
Examples:

SQL> SELECT COUNT(*) FROM EMP;
SQL> SELECT COUNT(JOB),COUNT(DISTINCT JOB) FROM EMP;
SQL> SELECT COUNT(SAL),COUNT(COMM) FROM EMP;