CODEWITHSUNDEEP
sqlsql-server-2008
Tommy Sayugo
Tommy Sayugo

Reputation: 375

How to Select X, SUM (X)?

Lets say I have a table like this:

ID_COMPANY   ID_EMPLOYEE  SALARY
COMP0001     EMP0001      200
COMP0001     EMP0002      100
COMP0001     EMP0003      300
COMP0002     EMP0001      200
COMP0002     EMP0003      200
COMP0003     EMP0002      200

I want to add a column to my table so it can SUM salary from based on ID_Employee like this:

ID_COMPANY   ID_EMPLOYEE  SALARY  TOTAL
COMP0001     EMP0001      200     400
COMP0001     EMP0002      100     300
COMP0001     EMP0003      300     500
COMP0002     EMP0001      200     400
COMP0002     EMP0003      200     500
COMP0003     EMP0002      200     300

Thanks in advance

Upvotes: 1

Views: 1434

Answers (4)

Sagar
Sagar

Reputation: 275

Select *, SUM(Salary) Over (Partition by ID_employee) Total
From YourTable

Upvotes: 0

dinomix
dinomix

Reputation: 976

select id_employee, sum(salary) from [table] group by id_employee

This will not 'add' the column to the table, but will output the result. Since this data would change if a new record is added it would be better to generate it dynamically with a query. If you must store it, you should store the result in a materialized view or a temporary table, not add the column to the original table.

Upvotes: 2

Andrey Korneyev
Andrey Korneyev

Reputation: 26886

You can pre-calculate amounts groupped by employee in CTE and then join that CTE in your query:

;with cte_Totals as
(
    select ID_EMPLOYEE, sum(SALARY) as TOTAL
    from your_table
    group by ID_EMPLOYEE
)
select 
    ID_COMPANY, 
    ID_EMPLOYEE, 
    SALARY,
    T1.TOTAL
from your_Table as T
    left outer join cte_Totals as T1 on T1.ID_EMPLOYEE = T.ID_EMPLOYEE

Upvotes: 2

DeadlyJesus
DeadlyJesus

Reputation: 1533

You can Join the table to itself and use one of them to do the SUM:

SELECT ID_COMPANY, ID_EMPLOYEE, SALARY, sub.SumSalary
FROM Table t JOIN (SELECT ID_EMPLOYEE, SUM(SALARY) as SumSalary
                   FROM Table
                   GROUP BY ID_EMPLOYEE) sub ON t.ID_EMPLOYEE = sub.ID_EMPLOYEE

Upvotes: 0

Related Questions