Saturday, 13 July 2013

SQL – Aggregate & Analytic Functions

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

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

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

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

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ORDER BY deptno;
Here is sample output
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.
analytic_function([ arguments ]) OVER (analytic_clause)
Explore more on this and your SQL will be awesome.