Group by on joined tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by on joined tables

Author

Topic

zokhox
Starting Member

4 Posts

Posted - 2012-06-27 : 21:00:06

Hi
I have 3 tables as:
Worker (WorkerID, WorkerName),
WorkOnProject (ProjectID, WorkerID, NumberDays)
AbsentOnProject (ProjectID, WorkerID, AbsentDays) "AbsentDays is a negative number"

I need to write a query which shows WorkerID, WorkerName and a column which shows the result of (NumberDays + AbsentDays) for each worker.

Please note that there could be multiple Absent records for each worker in the AbsentOnProject table. So I think I should Sum AbsentDays for each worker first then add the result to NumberDays in the WorkOnProject table

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-27 : 22:11:16

Have you tried writing a query yet? If so, it's helpful to post so we know what you're trying to do. Not sure if this is what you want:

declare @Worker table (WorkerID int, WorkerName varchar(20))
declare @WorkOnProject table (ProjectID int, WorkerID int, NumberDays int)
declare @AbsentOnProject table (ProjectID int, WorkerID int, AbsentDays int) --"AbsentDays is a negative number"

insert into @Worker (WorkerID, WorkerName)
values
(1, 'Jimmy John'),
(2, 'Ding Dong'),
(3, 'Goggles Google')

insert into @WorkOnProject (ProjectID, WorkerID, NumberDays)
values
(1, 1, 10),
(1, 2, 5)

insert into @AbsentOnProject (ProjectID, WorkerID, AbsentDays)
values
(1, 2, -3),
(1, 3, -10)

select W.WorkerID, W.WorkerName, SUM(COALESCE(WOP.NumberDays,0)) AS NumDays, SUM(COALESCE(AOP.AbsentDays,0)) AS AbsDays, SUM(COALESCE(WOP.NumberDays,0))+SUM(COALESCE(AOP.AbsentDays,0)) AS Total
from @Worker W
left join @WorkOnProject WOP ON WOP.WorkerID=W.WorkerID
left join @AbsentOnProject AOP ON AOP.ProjectID=WOP.ProjectID AND AOP.WorkerID=W.WorkerID

group by W.WorkerID, W.WorkerName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources