Aggregation in Oracle PL/SQL

Oracle 12 uses normal SQL statements when doing aggregation.  The standard rules for aggregating in SQL apply.  You need to group by all non aggregated columns and having clause will be evaluated after the group by operator.

Some examples of aggregation in Oracle PL/SQL are below.

Count Aggregation

The above example shows an INNER JOIN between two tables and COUNT the number of rows where for each unique value of eventno and dateheld.

Aggregation with Having clause

The above example will COUNT the number of lines for each value in column planno and will SUM up the value in numberfld for each value in column planno. After doing the SUM and COUNT aggregation, the query will then look at the HAVING clause and remove any rows where the COUNT of lineno is not greater than 7.

Oracle 12c supports all of the normal aggregation functions in SQL.  The most common are COUNT, SUM, AVG – the mean of the values, MIN, MAX, MEDIAN, VARIANCE, STDDEV – standard deviation of the values, RANK, DENSE_RANK and PERCENT_RANK.

A complete list of all Aggregation functions supported by Oracle 12c can be located here. Oracle Aggregation Functions.

I hope you found the above informative, let me know in the comments below.

— michael.data@eipsoftware.com