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.SessionI D = table2.sessionI D
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 abuse@mosey.com
The sun rises in the east, dumbass

[John Bell]

Re: Totaling Group bys?

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/default.asp?url=/library/en-us/acdata/ac_8_qd_09_610z.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp?frame=true

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

John

"The Only Mosey" <john@mosey.com muicate> wrote in message
news:bmicmm02jk k@drn.newsguy.c om...[color=blue]
> 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.SessionI D = table2.sessionI D
> 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 abuse@mosey.com
> The sun rises in the east, dumbass
>[/color]

[Simon Hayes]

Re: Totaling Group bys?

The Only Mosey <john@mosey.com muicate> wrote in message news:<bmicmm02j kk@drn.newsguy. com>...[color=blue]
> 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.SessionI D = table2.sessionI D
> 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 abuse@mosey.com
> The sun rises in the east, dumbass[/color]

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.registration id, sum(t1.hours)
from table2 t2 join table1 t1
on t2.sessionid = t1.sessionid
group by t2.registration id

-- drop table table1
-- drop table table2

Simon