CODEWITHSUNDEEP
sqlsql-server-2008
Chow.Net
Chow.Net

Reputation: 593

SUM() data in a column based on another column data

I have a sql table 

Project ID       Employee ID          Total Days

1                   100                   1
1                  100                   1
1                  100                   2
1                   100                   6
1                   200                   8
1                   200                   2

Now i need this table to look like

Project ID       Employee ID          Total Days

1                   100                   10
1                   200                   10

As iam new to sql,i am little confuse to use SUM() based on above condition.

Upvotes: 7

Views: 55203

Answers (3)

Fathah Rehman P
Fathah Rehman P

Reputation: 8761

select min("Project ID")as 'Project ID',"Employee ID"
   , SUM("Total Days") as 'Total Days'
from table1
group by "Employee ID"

Upvotes: 1

Niladri Biswas
Niladri Biswas

Reputation: 4171

Here are two approaches

Declare @t Table(ProjectId Int, EmployeeId Int,TotalDays Int)
Insert Into @t Values(1,100,1),(1,100,1),(1,100,2),(1,100,6),(1,200,8),(1,200,2)

Approach1:

Select ProjectId,EmployeeId,TotalDays = Sum(TotalDays)
From @t 
Group By ProjectId,EmployeeId

Approach2:

;With Cte As(
Select 
    ProjectId
    ,EmployeeId
    ,TotalDays = Sum(TotalDays) Over(Partition By EmployeeId)
    ,Rn = Row_Number() Over(Partition By EmployeeId Order By EmployeeId)
From @t )
Select ProjectId,EmployeeId,TotalDays
From Cte Where Rn = 1

Result

ProjectId   EmployeeId  TotalDays
1            100        10
1            200        10

Upvotes: 6

John Woo
John Woo

Reputation: 263893

This query below produces two columns: EmployeeID, totalDays.

SELECT  EmployeeID, SUM(totalDays) totalDays
FROM    tableName
GROUP BY EmployeeID

follow-up question: why is in your desired result the projectId is 1 and 2?

Upvotes: 6

Related Questions