Difference Between Analytical and Aggregate Functions in SQL - Oracle Interview Questions

Hello Friends, We are going to talk about one important question which is being asked in almost every SQL developer interviews.

While in college you would have used Aggregate functions and also you would have seen such questions in your semester labs and Viva.

In corporate setup you would have used Analytical functions as well. Sometime during interview, interviewer will ask some question which you might have used in day to day work but you may not know the name of that function. 

Same incident happened with me, Interviewer asked me what is analytical function and how it is different than aggregate function. 

I was wondering that Aggregate function I know....😟, but what is this Analytical function. I asked for a hint and I got embarrassed. I knew this Analytical function by its use, but I was not aware that these functions have a name called 'Analytical function' 😁. 

So check below table of content if you were looking for something specific else you can read through entire post. Welcome to the post !

Table of Contents

• What does aggregate function mean?
• What is an example of an aggregate?
• What are the analytical functions in SQL?
• What is an example of analytical functions in Oracle?
• Can we use analytical function in where clause?
• Analytical vs Aggregate function

What does aggregate function mean?

In common language you can understand Aggregate functions as a way or mathematical computation to get a single value or single row result by processing a set of data. These functions are very helpful when there is a need of finding aggregation. I mean to say you can use these functions when you need to find aggregate out of sample data, like you can find out total count, total sum, which is maximum, minimum and also an average of sample data. I am going to show you some example and syntax for each aggregate function.

HAVING Clause

The HAVING clause is generally used along with the GROUP BY clause. This clause is used in the column operation and is applied to aggregate rows or groups according to given conditions.

What is an example of an aggregate?

These are the examples of aggregate functions: 

• To Calculate Average -- avg()
• To Count records -- count()
• To Find Maximum -- Max()
• To Find minimum Min -- Min()
• To find out Sum -- Sum()

SYNTAX of aggregate functions:

We have SI_EMPLOYEE table and some of data to execute aggregate function,

SQL >select * from si_employee;

    EMP_ID EMP_NAME             EMP_SALARY   EMP_DEPT

---------- -------------------- ---------- ----------

     10001 Ritesh                     5000         20

     10002 Rakesh                     6000         20

     10003 Ramesh                     6000         10

     10004 Yash                       9000         10

     10005 Yamini                     8000         10

     10006 Bhavani                    9000         30

     10007 Tina                      19000         30

     10008 Ayyappa                   29000         10

8 rows selected.

SUM() :-  To find out sum of salary of each department.

select sum(emp_salary),emp_dept from SI_EMPLOYEE GROUP BY EMP_DEPT;

SUM(EMP_SALARY)   EMP_DEPT

--------------- ----------

          52000         10

          11000         20

          28000         30

AVG(): To find out average salary of employee in department 30, this case will show you the use of Having clause. After you have used group by clause, you will have to use Having clause to again filter particular row from already grouped data set.

SQL >select avg(emp_salary),emp_dept from SI_EMPLOYEE GROUP BY EMP_DEPT;

AVG(EMP_SALARY)   EMP_DEPT

--------------- ----------

          13000         10

           5500         20

          14000         30

SQL >select avg(emp_salary),emp_dept from SI_EMPLOYEE GROUP BY EMP_DEPT HAVING emp_dept=30;

AVG(EMP_SALARY)   EMP_DEPT

--------------- ----------

          14000         30

COUNT(): To find count of employee.

SQL >SELECT COUNT(EMP_ID) from SI_EMPLOYEE;

COUNT(EMP_ID)

-------------

            8 

MAX(): To find Maximum salary of employee.

SQL >SELECT MAX(EMP_SALARY)  from SI_EMPLOYEE WHERE EMP_DEPT=30;

MAX(EMP_SALARY)

---------------

          19000

MIN(): To find Minimum Salary of employee.

SQL >SELECT MIN(EMP_SALARY)  from SI_EMPLOYEE WHERE EMP_DEPT=30;

MAX(EMP_SALARY)

---------------

          9000

What are the analytical functions in SQL?

Analytic functions compute an aggregate value based on a group of rows and return multiple rows for each group. 

As it's name is analytical function, it means it will help in analysis. We used aggregate function to aggregate (group) data based on requirement. It has returned single result after processing. Now suppose we have to analyse data and find ranking of employee based on salary or salary among different department. Normal aggregate function will not be sufficient for this. We need to use analytical functions. Let us see some example. ROW_NUMBER(), RANK(), DENSE_RANK() etc.

What is an example of analytical functions in Oracle?

SQL >select emp_name,emp_salary,emp_dept ,ROW_NUMBER() OVER(ORDER BY emp_salary) RowNumber FROM(select emp_name,emp_salary,emp_dept from SI_EMPLOYEE GROUP BY emp_name,emp_salary,emp_dept);
EMP_NAME             EMP_SALARY   EMP_DEPT  ROWNUMBER
-------------------- ---------- ---------- ----------
Ritesh                     5000         20          1
Rakesh                     6000         20          2
Ramesh                     6000         10          3
Yamini                     8000         10          4
Bhavani                    9000         30          5
Yash                       9000         10          6
Tina                      19000         30          7
Ayyappa                   29000         10          8
8 rows selected.

SQL >select emp_name,emp_salary,emp_dept ,RANK() OVER(PARTITION BY EMP_DEPT ORDER BY emp_salary DESC) Rank FROM (select emp_name,emp_salary,emp_dept from SI_EMPLOYEE GROUP BY emp_name,emp_salary,emp_dept);

EMP_NAME             EMP_SALARY   EMP_DEPT       RANK
-------------------- ---------- ---------- ----------
Ayyappa                   29000         10          1
Yash                       9000         10          2
Yamini                     8000         10          3
Ramesh                     6000         10          4
Rakesh                     6000         20          1
Ritesh                     5000         20          2
Tina                      19000         30          1
Bhavani                    9000         30          2
8 rows selected.

Can we use analytical function in where clause?

Yes we can use analytic function in where clause. But not directly. We can use sub-queries and just use the analytical column in where clause. See example.

SQL >SELECT * FROM (select emp_name,emp_salary,emp_dept ,RANK() OVER(PARTITION BY EMP_DEPT ORDER BY emp_salary DESC) Rank FROM (select emp_name,emp_salary,emp_dept from SI_EMPLOYEE GROUP BY emp_name,emp_salary,emp_dept) )x WHERE x.RANK=2;

EMP_NAME             EMP_SALARY   EMP_DEPT       RANK

-------------------- ---------- ---------- ----------

Yash                       9000         10          2

Ritesh                     5000         20          2

Bhavani                    9000         30          2

Read More Here on Analytical Functions on Oracle Website

Analytical vs Aggregate function

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the Analytic_clause. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Next topic will be Case based DML testing.