Totaling Group bys?

[The Only Mosey]

Table1:
SessionID int
Hours int
....
SessionID Hours
111 3
222 2
333 3
444 2

Table2:
SessionID int
RegistrationID int
....
RegistrationID SessionID
888 111
888 444
777 111
666 222
666 333

I want to sum the hours for each person spent in sessions.

Results I'd like to see:
RegistrationID hours
888 5
777 3
666 4

What is not working:
Select hours, registrationID
from
table1, table2
where
table1.SessionID = table2.sessionID
Group by registrationID, hours

This adds up ALL the hours and then groups them by person.

Any help?

John Mosey "Humping your theoretical mom since 1993"
Complaints can be sent to ab***@mosey.com
The sun rises in the east, dumbass

[John Bell]

Hi

There are plenty of examples in books online for how to do select
statements.

Check out the information regarding joining tables and the select statement
at
http://msdn.microsoft.com/library/de...qd_09_610z.asp

http://msdn.microsoft.com/library/de...asp?frame=true

The following should give you the result required:
Select SUM(t.hours) as Hours, s.registrationID
from table1 t JOIN table2 s ON t.SessionID = s.sessionID
Group by s.registrationID

John

"The Only Mosey" <jo**@mosey.commuicate> wrote in message
news:bm*********@drn.newsguy.com...

Table1:
SessionID int
Hours int
...
SessionID Hours
111 3
222 2
333 3
444 2

Table2:
SessionID int
RegistrationID int
...
RegistrationID SessionID
888 111
888 444
777 111
666 222
666 333

I want to sum the hours for each person spent in sessions.

Results I'd like to see:
RegistrationID hours
888 5
777 3
666 4

What is not working:
Select hours, registrationID
from
table1, table2
where
table1.SessionID = table2.sessionID
Group by registrationID, hours

This adds up ALL the hours and then groups them by person.

Any help?

John Mosey "Humping your theoretical mom since 1993"
Complaints can be sent to ab***@mosey.com
The sun rises in the east, dumbass

[Simon Hayes]

The Only Mosey <jo**@mosey.commuicate> wrote in message news:<bm*********@drn.newsguy.com>...

Table1:
SessionID int
Hours int
...
SessionID Hours
111 3
222 2
333 3
444 2

Table2:
SessionID int
RegistrationID int
...
RegistrationID SessionID
888 111
888 444
777 111
666 222
666 333

I want to sum the hours for each person spent in sessions.

Results I'd like to see:
RegistrationID hours
888 5
777 3
666 4

What is not working:
Select hours, registrationID
from
table1, table2
where
table1.SessionID = table2.sessionID
Group by registrationID, hours

This adds up ALL the hours and then groups them by person.

Any help?

John Mosey "Humping your theoretical mom since 1993"
Complaints can be sent to ab***@mosey.com
The sun rises in the east, dumbass

create table table1 (sessionid int, hours int)
insert into table1 select 111, 3
insert into table1 select 222, 2
insert into table1 select 333, 3
insert into table1 select 444, 2

create table table2 (registrationid int, sessionid int)
insert into table2 select 888, 111
insert into table2 select 888, 444
insert into table2 select 777, 111
insert into table2 select 666, 222
insert into table2 select 666, 333

select t2.registrationid, sum(t1.hours)
from table2 t2 join table1 t1
on t2.sessionid = t1.sessionid
group by t2.registrationid

-- drop table table1
-- drop table table2

Simon