Introduction to Analytic Functions in Oracle

Published on
3,336 Points
Last Modified:
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.

Analytic functions are used to calculate or compute aggregate values based on a group of rows, it is also called as a Window. This determines the range of rows used to perform the calculations from the current row. The analytic functions only work on top of the results that are retrieved from the SQL queries. This helps us to analyze the data much easier.

These types of functions also help us in calculating cumulative totals. The most common use of these functions on identifying the ranks for example if we need to know the top persons who are highly paid or top 5 sales executive those who have done the best sales for a year. Their functions are not affected by “Group by” or “Where” or “Having” clause in Oracle.

Main differences between analytic and aggregate functions

The main difference between these two functions is that aggregate functions are used to return a single result value based on a group of rows. For example, if we need to identify the total number of audits till date from Audit table, then we need to use count function to determine the number of row in audit object. Now if we use the analytic way then this will return multiple rows with certain results within a window.

Examples below show how to retrieve the results from an audit table using analytic function and on aggregate function.

Below is the select clause using aggregate function “sum” with expression list i.e. Salary column from Employee table joining with from clause returns the total amount spent by a company on salary.

Now let us see if we can use analytical functions on the same table to get different results.

From the above SQL, we can see that the same Sum functions are now used as analytic functions to get the sum, but on cumulative salary. When we use the analytic function, we must use “OVER” and we also need to use order by or partition by as analytic.

This article was just an introduction to Oracle Analytic Functions. In my next article, I will be writing more on how to use and perform data analysis using analytical functions.

Thank you for reading my article, please feel free to leave me some feedback or to suggest any future topics.

I'll be looking forward to hearing from you – Swadhin Ray (Sloba)

For more information about me, please check out my Experts Exchange Profile page.

Author:Swadhin Ray

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Join & Write a Comment

Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month