Thursday, July 3, 2008

Partition By Null



I saw 'Partition by Null' in a snippet of code on the web, but with no explanation. Well, I'm so glad I tried this out, because as shown in the code below, this will give a calculation on a row basis.

With out the 'Partition By' , attempting to sum by row would display 'not a single-group group function.' 'Over (Partition By Null)' can also be written as simply 'Over()'.
Break On Report
Compute Sum Of -
Sal -
PctSal On Report

Column Pctsal Heading '% Of Total|Salary' Format 999

Select Ename,
Sal,
-----------------------------------------
100*Sal/Sum(Sal)
Over (Partition By Null)
As
Pctsal
-----------------------------------------
From Emp
Order By Sal Desc;
See Oracle documentation for further detail:
Partition By

No comments:

Post a Comment