Multiple row functions work upon group of rows and return one result for the complete set of rows. They are also known as Group functions or Aggregate functions.

Aggregate functions can appear in SELECT lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement.

When GROUP BY clause is used in a SQL query, Oracle database divides the rows into groups based on the columns specified in the GROUP BY clause. Oracle then applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

Let us discuss the following in our next chapters.

GROUP BY Clause
HAVING Clause
MAX
MIN
SUM
COUNT
AVG