Compute sum of in plain Oracle SQL

Once again a post about Oracle analytics. Indeed the best thing since sliced bread.

In sqlplus we have the posibility to insert breaks and to trigger basic events on these breaks. However in plain SQL it is not as easy, not that it is very hard either. Using analytics and the cool classic "decode rownum" hack we can get sort of the same results. On the last returned row you will get the full sum of the previous rows, in a new column however. But at least it's all done at the database layer and in plain SQL.

An example as usual

SQL> break on report
SQL> compute sum of sal on report
SQL> select ename, sal from emp
  2  /

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
JAMES
           ----------
sum             29025

15 rows selected.

SQL> clear breaks
breaks cleared
SQL> select e.*, (decode(rownum,count(*)over(),sum(e.sal)over(),null)) sum
  2  from (select ename, sal from emp union 
  3  select null,null from dual order by ename) e
  4  /

ENAME             SAL        SUM
---------- ---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2850
CLARK            2450
FORD             3000
JAMES             950
JAMES
JONES            2975
KING             5000
MARTIN           1250
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500
WARD             1250
                           29025

16 rows selected.

Update
You can also use the "group by rollup" features, I've writen a small article about it here.