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?
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)--------------- 19000MIN(): 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 2Ritesh 5000 20 2Bhavani 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.
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.
- To Calculate Average -- avg()
- To Count records -- count()
- To Find Maximum -- Max()
- To Find minimum Min -- Min()
- To find out Sum -- Sum()
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.
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.
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.
0 Comments
Please wait, we will address your query shortly