**Summary**: in this tutorial, you will learn how Oracle aggregate functions work and how to apply them to calculate aggregates.

## Introduction to Oracle aggregate functions

Oracle aggregate functions calculate on a group of rows and return a single value for each group.

We commonly use the aggregate functions together with the `GROUP BY`

clause. The `GROUP BY`

clause divides the rows into groups and an aggregate function calculates and returns a single result for each group.

If you use aggregate functions without a `GROUP BY`

clause, then the aggregate functions apply to all rows of the queried tables or views.

We also use the aggregate functions in the `HAVING`

clause to filter groups from the output based on the results of the aggregate functions.

Oracle aggregate functions can appear in `SELECT`

lists and `ORDER BY`

, `GROUP BY`

, and `HAVING`

clauses.

## DISTINCT vs. ALL

Some aggregate functions accept `DISTINCT`

or `ALL`

clause.

- The
`DISTINCT`

clause instructs an aggregate function to consider only distinct values of the argument. - The
`ALL`

clause causes an aggregate function to take all values into the calculation, including duplicates.

For example, the `DISTINCT`

average of 2, 2, 2 and 4 is 3, which is the result of (2 + 4) / 2. However, the `ALL`

average of 2, 2, 2 and 4 is 2.5, which is the result of (2 + 2 + 2 + 4) / 4.

Oracle uses the `ALL`

clause by default if you don’t explicitly specify any clause.

## NULL treatments

All aggregate functions ignore null values except `COUNT(*)`

, `GROUPING()`

, and `GROUPING_ID()`

.

If you want to substitute a value e.g., zero for a null value, you use the `NVL()`

function.

The `COUNT()`

and `REGR_COUNT()`

functions never return null, but either a number or zero (0). Other aggregate functions return `NULL`

if the input data set contains `NULL`

or has no rows.

## Oracle aggregate function list

The following table illustrates the aggregation functions in Oracle:

Function | Description |
---|---|

APPROX_COUNT_DISTINCT | |

AVG | Return the average of values of a set |

COLLECT | |

CORR | |

CORR_* | |

COUNT | Return the number of values in a set or number of rows in a table |

COVAR_POP | |

COVAR_SAMP | |

CUME_DIST | |

DENSE_RANK | |

FIRST | |

GROUP_ID | |

GROUPING | |

GROUPING_ID | |

LAST | |

LISTAGG | Aggregate strings from multiple rows into a single string by concatenating them |

MAX | Return the maximum value in a set of values |

MEDIAN | |

MIN | Return the minimum value in a set of values |

PERCENT_RANK | |

PERCENTILE_CONT | |

PERCENTILE_DISC | |

RANK | |

REGR_ (Linear Regression) Functions | |

STATS_BINOMIAL_TEST | |

STATS_CROSSTAB | |

STATS_F_TEST | |

STATS_KS_TEST | |

STATS_MODE | |

STATS_MW_TEST | |

STATS_ONE_WAY_ANOVA | |

STATS_T_TEST_* | |

STATS_WSR_TEST | |

STDDEV | |

STDDEV_POP | |

STDDEV_SAMP | |

SUM | Returns the sum of values in a set of values |

SYS_OP_ZONE_ID | |

SYS_XMLAGG | |

VAR_POP | |

VAR_SAMP | |

VARIANCE | |

XMLAGG |

In this tutorial, you have learned about Oracle aggregate functions and how to use them to calculate aggregates.