An aggregate function, as the name suggests, aggregates data from
several rows into a single result row. Like, Average, Count, Sum or
addition, etc.

Now, add

Actually, this will give you error first. Because, all the entities used in Group By clause has to be present in the SELECT statement. So the whole statement will look as

Thus, aggregate function also reduces the number of rows returned.

Now that you know what is aggregate function, it is simple to understand what is analytic function. Analytic function also work in similar ways as Aggregate function with Group BY clause and reduce the number of rows returned.

This group of rows, also called "window", operate on set of rows defined by OVER clause. Hence, Analytic functions are also referred as Windowing functions.

Syntax:

This will give output as single value denoting Average salary in the organization.SELECT Avg(Salary) FROM emp;

Now, add

This will give output as average salaries for all deparements.GROUP BY Dept_no;

Actually, this will give you error first. Because, all the entities used in Group By clause has to be present in the SELECT statement. So the whole statement will look as

Here is sample outputSELECT deptno, AVG(sal) FROM emp GROUP BY deptno ORDER BY deptno;

Thus, aggregate function also reduces the number of rows returned.

Now that you know what is aggregate function, it is simple to understand what is analytic function. Analytic function also work in similar ways as Aggregate function with Group BY clause and reduce the number of rows returned.

SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal FROM emp;

This group of rows, also called "window", operate on set of rows defined by OVER clause. Hence, Analytic functions are also referred as Windowing functions.

Syntax:

Explore more on this and your SQL will be awesome.analytic_function([ arguments ]) OVER (analytic_clause)