Query help - Joining 2 tables...summing from one 2

[Sypher2]

I have two tables. I want to write a SQL statement which combines info from both. I'm having trouble.

Table 1 fields:
1. ID
2. Name
3. Stat_1
4. TimeStamp

Table 2 fields:
1. ID
2. Stat_2
3. Stat_3
4. TimeStamp

Table 1 will have one record per ID per TimeStamp. Table 2 may have multiple identical TimeStamps per ID. Table 2 may also have zero records corresponding to the timestamp in Table 1. I am trying to get a query to output:

1. ID
2. TimeStamp
3. Stat_1
4. Sum(Stat_2)
5. Sum(Stat_3)

If there is no record in Table 2, I want #4 & 5 to be zero.

Thanks for any help

 

[SQLSister]

derived table is the way to go here.

Select a.ID, Name, Stat_1, sumstat2, sumstat3 from Table1 a
left join
(select sum(Stat_2)as sumstat2, sum(Stat_3) as sumstat3 from table2 group by ID) b
on a.id = b.ID

Look at the isnull function in BOL to see how to show a zero if there is no sum. You will need it both in the dervived table and the select at the top.


Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[r937]

i think that "Table 2 may also have zero records corresponding to the timestamp in Table 1" implies that the join must be on both ID and Timestamp

select Table1.ID
     , Table1.TimeStamp
     , Table1.Stat_1
     , Sum(Table2.Stat_2) as Sum_Stat_2
     , Sum(Table2.Stat_3) as Sum_Stat_3
  from Table1
left outer
  join Table2
    on Table2.ID = Table1.ID
   and Table2.Timestamp = Table1.Timestamp
group
    by Table1.ID
     , Table1.TimeStamp
     , Table1.Stat_1

r937.com | rudy.ca

 

[SQLSister]

Oops I missed part of what I wanted to post. Bad day at awork, too tired to think straight. The derived table needs to have the id field as part of the select as well.

Select a.ID, Name, Stat_1, sumstat2, sumstat3 from Table1 a
left join
(select id, sum(Stat_2)as sumstat2, sum(Stat_3) as sumstat3 from table2 group by ID) b
on a.id = b.ID

If rudy is right then this might work

Select a.ID, Name, Stat_1, sumstat2, sumstat3 from Table1 a
left join
(select id, timestamp, sum(Stat_2)as sumstat2, sum(Stat_3) as sumstat3 from table2 group by ID, timestamp) b
on a.id = b.ID and a.timestamp = b.timestamp

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[vongrunt]

Personally I prefer derived tables too:

select A.id, A.TimeStamp, A.Stat_1, 
	isnull(X.Stat_2, 0) as Stat_2,
	isnull(X.Stat_3, 0) as Stat_3
from Table1 A
left outer join
(	select ID, TimeStamp, Sum(Stat_2) as Stat_2, Sum(Stat_3) as Stat_3
	from TableB 
	group by ID, TimeStamp
) X
on A.ID = X.ID and A.TimeStamp = X.TimeStamp

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)