sql select query to calculate sum.

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

sql select query to calculate sum.

Author

Topic

gtester
Starting Member

4 Posts

Posted - 2010-10-01 : 16:33:00

hello all,
i'm new..pl help me..
i've 2 tables:
t1-
userid1
calendarday1
workhours1

t1 values-
abc1, 3, 7/1/2010
abc1, 5, 7/2/2010
abc1, 7, 7/3/2010

t2-
userid2
calendarday2
workhours2

t2 values-
abc1, 8, 7/1/2010
abc1, 6, 7/4/2010
abc1, 4, 7/5/2010

i want result like below..sum the workhours when calendarday1=calendarday2; and also display workhours for calendarday1 & calendarday2..

abc1, 11, 7/1/2010
abc1, 5, 7/2/2010
abc1, 7, 7/3/2010
abc1, 6, 7/4/2010
abc1, 4, 7/5/2010

thax.

jleitao
Posting Yak Master

100 Posts

Posted - 2010-10-01 : 17:05:34

hi gtester,

SELECT
userid1,
calendarday1,
SUM(workhours1)

FROM(
SELECT
userid1,
calendarday1,
workhours1
from T1

UNION

SELECT
userid2 as userid1,
calendarday2 as calendarday1,
workhours2 as workhours1
from T2
) AUX
group by
userid1,
calendarday1

i dont test it but i think it works.

Jleitao

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-10-01 : 19:44:47

Try this.

declare @t1 table(userid1 varchar(20),workhours1 int, calendarday1 datetime)
insert into @t1 values('abc1', 3, '7/1/2010')
insert into @t1 values('abc1', 5, '7/2/2010')
insert into @t1 values('abc1', 7, '7/3/2010')
declare @t2 table(userid2 varchar(20),workhours2 int, calendarday2 datetime)
insert into @t2 values('abc1', 8, '7/1/2010')
insert into @t2 values('abc1', 4, '7/4/2010')
insert into @t2 values('abc1', 6, '7/5/2010')

select
coalesce(userid1,userid2) as userid,
case when calendarday1=calendarday2 then (workhours1+workhours2) else coalesce(workhours1,workhours2) end as workhour,
convert(varchar(10),coalesce(calendarday1,calendarday2),101)as calendarday
from @t1 full outer join @t2 on userid1=userid2 and calendarday1=calendarday2

--result
userid workhour calendarday
-------------------- ----------- -----------
abc1 11 07/01/2010
abc1 5 07/02/2010
abc1 7 07/03/2010
abc1 4 07/04/2010
abc1 6 07/05/2010

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources