Analytical SQL - SUM with Partition By

Welcome to the second post on the series Analytical SQL. 

In the first post we studied Analytical Function without Window Clause. With this post, we will learn Partition By. 

Analytical functions compute an aggregated value based on a group of rows defined as per Partition By clause, which determines the range of rows used to perform the calculations. 

In last post, we calculated TOTAL SAL in EMP table against each row, using

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

Now, I want to modify TOTAL SAL to DEPARTMENT TOTAL SAL, i.e. calculate total salary of the department against department of each particular row.

SQL> select d.deptno, d.dname, empno, ename, sal,
  2  sum(sal) over(partition by d.dname) total
  3  from emp e, dept d
  4  where e.deptno = d.deptno;

    DEPTNO DNAME               EMPNO ENAME             SAL      TOTAL
---------- -------------- ---------- ---------- ---------- ----------
        10 ACCOUNTING           7839 KING          5000.15     8750.6
        10 ACCOUNTING           7934 MILLER           1300     8750.6
        10 ACCOUNTING           7782 CLARK         2450.45     8750.6
        20 RESEARCH             7566 JONES          2975.5   10877.41
        20 RESEARCH             7788 SCOTT         3000.55   10877.41
        20 RESEARCH             7902 FORD           3000.6   10877.41
        20 RESEARCH             7369 SMITH          800.75   10877.41
        20 RESEARCH             7876 ADAMS         1100.01   10877.41
        30 SALES                7521 WARD             1250    9403.19
        30 SALES                7654 MARTIN        1250.05    9403.19
        30 SALES                7844 TURNER        1500.95    9403.19
        30 SALES                7900 JAMES          950.99    9403.19
        30 SALES                7499 ALLEN          1600.9    9403.19
        30 SALES                7698 BLAKE          2850.3    9403.19

If you want to achieve above output without Analytical Function, you can also try

select d.deptno, d.dname, empno, ename, sal, total
from emp e, dept d,
(select sum(sal) total, deptno from emp group by deptno) t
where d.deptno = e.deptno
and d.deptno = t.deptno
order by 1;