ORACLE AGGREGATE VS ANALYTICAL FUNCTIONS

ORACLE AGGREGATE VS ANALYTICAL FUNCTIONS

For AGGREGATE functions, we always use GROUP BY clause with HAVING (Optional) for grouping the data according to the necessity. Below I am taking two examples. Please carefully see the difference

MIN      : For knowing the MINIMUM value,
MAX     : For knowing the MAXIMUM value, 
SUM      : For knowing the SUM value,
AVG       : For knowing the AVERAGE value, 
COUNT : For knowing number of COUNTS

In the Example -1, it is grouping the data according to the department number so the aggregate function was working only on the department wise but in Example-2, it is grouping the data according to the employee number & department number So the result set has changed






NOW WHEN DO WE USE ANALYTICAL FUNCTIONS
If we want the MIN, MAX, SUM, AVG, COUNT of the salary according to the department wise BUT we need to show how many employees are in departments with the actual  salary also so here in this case we need to use ANALYTICAL functions like below
 
GENERAL SYNTAX
 analytic_function([ arguments ]) OVER (analytic_clause) 

analytic_clause is further divide into the optional elements
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

1. The query_partition_clause divides the result set into partitions, or groups, of data.
2. The operation of the analytic function is restricted to the boundary imposed by these partitions, similar to the way a GROUP BY
    (Refer the Example-2 Above) clause affects the action of an aggregate function.
3. If the query_partition_clause is omitted, the whole result set is treated as a single partition.
4. If we change the OVER clause to include a query_partition_clause based on the department

-------