Oracle 12 uses normal SQL statements when doing aggregation.\u00a0 The standard rules for aggregating in SQL apply.\u00a0 You need to group by all non aggregated columns and having clause will be evaluated after the group by operator.<\/p>\n
Some examples of aggregation in Oracle PL\/SQL are below.<\/p>\n
<\/p>\n
SELECT eventrequest.eventno AS event_number\r\n ,eventrequest.dateheld\r\n ,COUNT(eventplan.planno) AS count_event_plans\r\nFROM eventrequest\r\n INNER JOIN eventplan\r\n ON eventrequest.eventno = eventplan.eventno\r\nWHERE eventplan.workdate BETWEEN '01-DEC-2013' AND '31-DEC-2013'\r\nGROUP BY eventrequest.eventno\r\n ,eventrequest.dateheld \r\n;<\/pre>\nThe above example shows an INNER JOIN<\/strong> between two tables and COUNT<\/strong> the number of rows where for each unique value of eventno and dateheld.<\/p>\n
Aggregation with Having clause<\/h5>\n
SELECT eventplan.planno\r\n ,COUNT(eventplanline.lineno) AS count_lines\r\n ,SUM(eventplanline.numberfld) AS sum_resources\r\nFROM eventplan\r\n INNER JOIN eventplanline\r\n ON eventplan.planno = eventplanline.planno\r\nGROUP BY eventplan.planno\r\nHAVING COUNT(eventplanline.lineno) > 7\r\n\r\n;<\/pre>\nThe above example will COUNT<\/strong> the number of lines for each value in column planno and will SUM<\/strong> up the value in numberfld for each value in column planno. After doing the\u00a0SUM<\/strong> and\u00a0COUNT<\/strong> aggregation, the query will then look at the\u00a0HAVING<\/strong> clause and remove any rows where the\u00a0COUNT<\/strong> of lineno is not greater than 7.<\/p>\n
Oracle 12c supports all of the normal aggregation functions in SQL.\u00a0 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.<\/p>\n
A complete list of all Aggregation functions supported by Oracle 12c can be located here. Oracle Aggregation Functions<\/a>.<\/p>\n
I hope you found the above informative, let me know in the comments below.<\/p>\n