Analytical SQL - SUM without Window Clause

This is my first post in 2022, and with this I am starting a new series on Analytical SQL. 

In The first example, we are trying to list all records from EMP and DEPT tables with the SUM of SAL column with each row. As you can see here, we have used "sum(sal) over()" without WINDOW ( partition by), which made Oracle to process all rows in table.

select d.deptno, d.dname, empno, ename, 
sum(sal) over() total
from emp e, dept d
where e.deptno = d.deptno;

    DEPTNO DNAME               EMPNO ENAME           TOTAL
---------- -------------- ---------- ---------- ----------
        10 ACCOUNTING           7839 KING          29031.2
        30 SALES                7698 BLAKE         29031.2
        10 ACCOUNTING           7782 CLARK         29031.2
        20 RESEARCH             7566 JONES         29031.2
        20 RESEARCH             7788 SCOTT         29031.2
        20 RESEARCH             7902 FORD          29031.2
        20 RESEARCH             7369 SMITH         29031.2
        30 SALES                7499 ALLEN         29031.2
        30 SALES                7521 WARD          29031.2
        30 SALES                7654 MARTIN        29031.2
        30 SALES                7844 TURNER        29031.2
        20 RESEARCH             7876 ADAMS         29031.2
        30 SALES                7900 JAMES         29031.2
        10 ACCOUNTING           7934 MILLER        29031.2

Above SQL is equivalent to

select d.deptno, d.dname, empno, ename, 
(select sum(sal) from emp) total
from emp e, dept d
where e.deptno = d.deptno;

Related Posts 

- Analytical Functions