Query for listing DeptNo, ename, sal, SUM(Total sal in that department)

Query for listing DeptNo, ename, sal, SUM(Total sal in that department)

In the above scenario it is asked to list DeptNo, ename, sal along a new column containing sum of salary of individual dept.

Suppose we have a table EMP, lets query the required column.

select DEPTNO,ENAME,SAL from EMP ORDER BY DEPTNO;

Here we can't apply GROUP BY clause directly because we have to print ENMAE and SAL too along with total salary belong to that department.











Lets write the final query.

SELECT a.DEPTNO, ENAME, SAL, (SELECT SUM(SAL) FROM EMP e WHERE a.DEPTNO = e.DEPTNO) as DeptSal FROM EMP a
ORDER BY a.DEPTNO;