CODEWITHSUNDEEP
sql
Naga Sushma Kallam
Naga Sushma Kallam

Reputation: 25

finding sum of salary from employee table

I'm having the data in data base like in the below.

Emp_id Ename Sal 
11      AAA   10
22      BBB   20
33      CCC   30
44      DDD   60

Then i want to populate a new column as in the below.

Emp_id Ename Sal  New_column
11      AAA   10    10  --> 10+0
22      BBB   20    30  --> 10+20
33      CCC   30    60  --> 10+20+30
44      DDD   60    120 --> 10+20+30+60

Thank you for the help in adavance!

Upvotes: 0

Views: 779

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

If you can use Window analytic function within your DBMS, then use :

select t.*, sum(sal) over (order by emp_id) as New_column 
  from tab t
 order by emp_id

Upvotes: 0

Ajeet Verma
Ajeet Verma

Reputation: 1123

Please use below query for the running aggregate in SQL Server.

SELECT Emp_id
    ,Ename
    ,Sal
    ,SUM(Sal) OVER (
        ORDER BY Emp_id
        ) AS New_column
FROM Employee
ORDER BY Emp_Id

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

Just in case your RDBMS does not support analytic functions (e.g. because you are using MySQL 5.7 or earlier), we can also use a correlated subquery to find the running totals:

SELECT
    Emp_id,
    Ename,
    Sal,
    (SELECT SUM(t2.Sal) FROM yourTable t2 WHERE t2.Emp_id <= t1.Emp_id) AS New_column
FROM yourTable t1
ORDER BY
    Emp_id;

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

I guess you want the running sum. If your DB product supports window function, You may try below - 

SELECT Emp_id
      ,Ename
      ,Sal 
      ,SUM(Sal) OVER(ORDER BY EMP_ID) New_column
FROM EMPLOYEE;

Upvotes: 0

Related Questions