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
DISTINCTclause instructs an aggregate function to consider only distinct values of the argument. - The
ALLclause 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.
