How To Use Oracle ANALYTIC Functions
  Home
 

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;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement analytic functions oracle analytic functions in oracle max oracle max in oracle mod oracle mod in oracle count oracle count in oracle min oracle min in oracle sum oracle sum in oracle rownum oracle rownum in oracle online colleges online

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 ) ;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement analytic functions oracle analytic functions in oracle max oracle max in oracle mod oracle mod in oracle count oracle count in oracle min oracle min in oracle sum oracle sum in oracle rownum oracle rownum in oracle online colleges online

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 ) ;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement analytic functions oracle analytic functions in oracle max oracle max in oracle mod oracle mod in oracle count oracle count in oracle min oracle min in oracle sum oracle sum in oracle rownum oracle rownum in oracle online colleges online

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 ) ;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement analytic functions oracle analytic functions in oracle max oracle max in oracle mod oracle mod in oracle count oracle count in oracle min oracle min in oracle sum oracle sum in oracle rownum oracle rownum in oracle online colleges online


See Also:
Home Oracle Select

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*