Online Tech Support

Oracle ANALYTIC functions can be use to do grouping on every row and without removing the duplications from the query output. We do recommend to use Oracle analytic functions on web pages or on reports and doing it your page does have to do download all lines to the page to do sum. You can just add additional column with Oracle analytic functions and use values from there.

The first examples will be used as a source query and to amend the data we are using the MOD function.

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

The following query shows how to use one of the Oracle analytic functions, named COUNT and as you see all lines are counted the result is displayed without grouping 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. The online tech support 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 the examples have applied Oracle analytic functions over all lines, but there are more possibilities to work with groups without grouping the data. On the following online tech support query we taking a sum of values for every value type 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 ) ;

See Also:
Home Oracle Select