# Analytical Function vs Aggregate function with example

In my previous article I have given the details about the aggregate functions in SQL with examples. In this article I would like to give the Analytical Function vs Aggregate function with multiple real life examples. The article will give you the difference between analytical and aggregate function with real industry examples. The analytical functions are functions which also returns the aggregate results like aggregate functions but those results are not group results. The analytical functions will group results for multiple items with each records. We will cover Analytical function vs aggregate function with real industry examples here.

## What is Mean by Analytical Functions?

The functions which are used for calculating the aggregate value for the specified groups in the table are called as analytical functions. The question in mind is how they differ from aggregate functions? – The analytical functions returns aggregate results for multiple rows in same group we can call those group of rows are window which defined by analytical clause. We require to define sliding window of rows to perform certain calculations with respect to the range. The window will define either by physical number of rows or logical rows.

The analytical functions can be defined only in Select clause or Order by clause of select statement. The Where clause,group by clause,Joins or having clause needs to be executed before analytical functions execution. The Key use of anlytical functions are finding cumulative totals or finding moving centered or reporting aggregate where we require the aggregate totals for specific groups.

Example :

``````select emp_no
,      sal
,      deptno
,      rank() over ( partition by dept_no
order by sal desc
) sal_rank
from employees;

Output :
EMPNO        SAL     DEPTNO   SAL_RANK
---------- ---------- ---------- ----------
1110       12450      10          1
1111       11300      10          2
1112       50100      20          1
1113       30010      20          2
1114       13000      20          2

you can also see the example of cumulative sum calculation using analytical functions.

``````

## What is Mean by Aggregate Functions?

The aggregate functions are functions which will return single result to group of results. It always returns single results for group of rows. The aggregate functions are most used functions in SQL. The aggregate functions can appear in select list and also order by and having clause. The aggregate functions are commonly used with group by clause where we require to group results and find the aggregate value for the same group. You can also eliminate the group results with having clause.

Real life example :

If you want to calculate Average salary of employees for IT department with multiple positions.

Select Avg(salary) from Employee where department=’IT’ group by position;

The above example will calculate the average salary for all IT employees by position.

## Analytical Function vs Aggregate function Tabular Format

This is actual difference between analytical function and aggregate function with examples. If you like this article or if you have issues with the same kindly comment in comments section.