Wednesday, June 28

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.

3 comments:

Janice said...

This is such a great article. You can also visit my posted topic articles which is about Male Enhancement Products and Health tips as well.

Melany Flemmings said...

Oh man! I will recommend your site to the other platforms. Thanks for the quality!

hotels downtown sacramento

Valentino thomas said...
This comment has been removed by the author.