Aggregate or Group functions in Oracle SQL

This Oracle tutorial point contains the information about the Aggregate functions or Grouping functions. The below is the list of functions discussed in the current post.
  1. Average / AVG function
  2. Summation / SUM function
  3. Maximum / MAX function
  4. Minimum / MIN function
  5. Standard Deviation / STDDEV function
  6. Variance function
  7. Count function
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:
  • The DISTINCT keyword indicates not to consider duplicate values.
  • ALL indicates to consider each and every value including duplicates.
  • Syntax: GROUP_FUNCTION_NAME(ALL /DISTINCT / n)
  • The data type for arguments may NUMBER, DATE, CHAR or VARCHAR.
  • All other group functions except COUNT(*) ignore NULL values. NVL function is used to substitute a NULL value.
  • When a group function is declared in a SELECT list, no single row columns should be declared.
  • Once a group function is declared in a SELECT list, other columns can be declared. But, they should be grouped columns and all non-functional columns should be declared into GROUP BY clause.
Average / AVG function:
As per the below syntax, it returns the average value of n and it ignores NULL values.

Syntax: AVG(DISTINCT / ALL / n)

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

Summation / 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)

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

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

Syntax: MAX(DISTINCT / ALL / n)

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

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

Syntax: MIN(DISTINCT / ALL /n)

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

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

Syntax: STDDEV(DISTINCT / ALL / n)

Example:
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)

Example:
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 returns all rows, including duplicated and NULLs. It can be used to specify the count of all rows or only distinct values of n.

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

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

If any queries or concerns about this post, please mention it in the comments section.

Comments