In-Oracle.com  -> Oracle SQL & PLSQL

-> Partition by in Oracle

 Partition by in Oracle

NOTE: I named "group by" column a column based on a function like SUM, COUNT, AVG, MAX, MIN (demands a "group by" clause in the SELECT statement ).

This can be done with the over() option:

Exemples:

select max(comm) over() as max_comm, empno, ename, comm from emp;

select empno, ename, max(sal) over() as max_sal, sal from emp;

SQL> select max(comm) from emp;

MAX(COMM)
-----------------
1400

SQL> select max(comm) over() as max_comm, empno, ename, comm from emp;

MAX_COMM   EMPNO   ENAME    COMM
----------            ----------  ----------   ----------
1400                   1               SMITH
1400                   2               ALLEN        300
1400                   3               WARD         500
1400                   4               JONES
1400                   5               MARTIN    1400
1400                   6               BLAKE
1400                   7               CLARK
1400                   8               SCOTT
1400                   9               KING
1400                 10               TURNER       10
1400                 11               ADAMS
1400                 12               JAMES
1400                 13               FORD
1400                 14               MILLER

You can also calculate a MAX per group:

select empno, ename, job, max(sal) over(partition by job ) as max_sal, sal from emp;

 EMPNO    ENAME      JOB                MAX_SAL         SAL
----------    ----------    ---------           ----------           ----------
  8                 SCOTT       ANALYST    3000                  3000
13                 FORD         ANALYST    3000                  3000
14                 MILLER     CLERK          1300                  1300
12                 JAMES       CLERK          1300                    950
  1                 SMITH       CLERK          1300                    999
11                 ADAMS     CLERK          1300                  1100
  6                 BLAKE       MANAGER   2975                  2850
  4                 JONES        MANAGER   2975                  2975
  7                 CLARK       MANAGER   2975                  2450
  9                 KING          PRESIDENT  5000                  5000
10                 TURNER     SALESMAN 1600                       12
  5                 MARTIN     SALESMAN 1600                   1250
  3                 WARD         SALESMAN 1600                   1250
  2                 ALLEN        SALESMAN 1600                   1600

In-Oracle.com  -> Oracle SQL & PLSQL

-> Partition by in Oracle