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

Analytical Functions	Aggregate Functions
No Collapse Rows : The analytical functions does not collapse the rows. The output of analytical function in same number of rows of the table which you are processing	Collapse in Rows: Aggregate function always have multiple rows as input and single row as output. It always returns the single reference value
Performance Queries: The performance perspective analytical queries run faster than aggregate functions as there are no sorts orders as in aggregate functions	Slow Queries: These queries are slower than analytical functions
Window Partitioning : The group of rows always defines in the partitioning we can call it as windows. We require to use window frame clauses	Group by Clause : The group of rows defined by SQL group by clause
Example : How to calculate cumulative sum in SQL. Query: select dept_no Department_no, count(empno) Employee_Per_Dept, sum(count(*)) over (order by deptno) Cumulative_Total from Employee group by deptno;	Example : If you require to calculate the groupwise count of employees from employee table. Query : Select Department_name,Count(emp_no) from Employees group by Department_name order by 1;

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.