How to display employee details along with sum(sal) of all employees?

Question

Is it possible to display employee details like the below using SQL?

Emp_id, Emp_name, Emp_Sal, Sum(Emp_Sal)

Note: Here Sum(Emp_Sal) is total sum of all the employees.

Answer 1

Arulkumar offered a correct solution, using a scalar subquery.

The solution below uses an analytic function. Notice the windowing clause: it is empty, since we want a single group (all employees), so there is no "partition by", and we want to add all rows, so there is no "order by" (which by default means all rows are ordered as "equal" and we get the sum over all rows).

The advantage is that you only go through the base table once. In the "scalar subquery" method, the table is read twice. It may not matter in this simple example, but it may matter a lot in production, with very large tables.

    select empno, msal, sum(msal) over () as total_sal from employees;



     EMPNO       MSAL  TOTAL_SAL
---------- ---------- ----------
      7369        800      28875
      7499       1600      28875
      7521       1250      28875
      7566       2975      28875
      7654       1250      28875
      7698       2850      28875
      7782       2450      28875
      7788       3000      28875
      7839       5000      28875
      7844       1500      28875
      7876       1100      28875
      7900        800      28875
      7902       3000      28875
      7934       1300      28875