Analytic functions are the last set of operations performed in a query
except for the final ORDER BY clause. All joins and all WHERE, GROUP
BY, and HAVING clauses are completed before the analytic functions are
processed. Therefore, analytic functions can appear only in the select
list or ORDER BY clause.
Aggregate functions are commonly used with the GROUP BY clause in a
SELECT statement, where Oracle Database divides the rows of a queried
table or view into groups. In a query containing a GROUP BY clause,
the elements of the select list can be aggregate functions, GROUP BY
expressions, constants, or expressions involving one of these. Oracle
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.