How To Use Analytic Functions In Oracle

This online tutorial is based on examples to make it easier to follow. Oracle Analytic functions will help you to use grouping functions on every single row and the query result will be done without grouping the values. Oracle DBA recommends to use Oracle Analytic functions on your web pages or doing reports and not to download all lines to website to do sum. You can add an extra column to your SQL query and apply Oracle Analytic functions. The final output calculated in the extra column can be used with just fetching the first row.

The first examples is a source query that we will use for the following examples. The query contains Oracle dual table and the pseudo column Rownum data is amended using Oracle MOD function.

 SELECT mod (rownum,3) AS amount
   FROM dual
CONNECT BY rownum < 21;

The following Oracle DBA query shows how to use Oracle COUNT function with Analytic function mode. As you see all lines are counted and the result “total_count” is done without grouping the data.

SELECT amount, COUNT(*) over () AS total_count
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
       CONNECT BY rownum < 21 ) ;

Oracle analytic functions can make the query simpler since we don’t need to worry about Oracle Group By clause. This example below shows how to receive minimum, maximum and sum using Oracle analytic functions.

SELECT amount,
       MIN(amount) over () AS min_amount,
       MAX(amount) over () AS max_amount,
       SUM(amount) over () AS sum_amount
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
       CONNECT BY rownum < 21 ) ;

So far all Oracle DBA examples have applied Oracle Analytic functions over all lines, but there are more options to work with groups without grouping the data. The following query we do Sum for every different value type group e.g. Zeros, ones and twos are making three different groups and we are applying the Oracle SUM function on every group separately.

SELECT amount,
       SUM(amount) over (partition by amount) AS sum_grouped_amounts
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
CONNECT BY rownum < 21 ) ;

There are option and functions to work with Oracle Analytic Functions take a look at Oracle web page for more examples.

See Also:
Oracle Select Oracle Home