Binaryworld - An aggregation problem in a topic, seminars & registration scenario. ... [ SQL Server -> T-SQL ]

create table #Topic (
    TopicID char(2)   not null primary key ,
    TopicName varchar(32) not null
)
create table #Seminar (
    SeminarID int   not null primary key ,
    TopicID char(2) not null foreign key references #Topic(#TopicID)
)
create table #Registrant (
    RegistrantID int not null primary key ,
    SeminarID  int not null foreign key references Seminar(SeminarID)
)
go
insert into #Topic values ( 'BR','How to be the Best Receptionist' )
insert into #Topic values ( 'AC','Advanced Coding'         )
insert into #Topic values ( 'IP','Insurance Processing'      )

insert into #Seminar values ( 1 , 'BR' )
insert into #Seminar values ( 2 , 'IP' )
insert into #Seminar values ( 3 , 'AC' )
insert into #Seminar values ( 4 , 'AC' )
insert into #Seminar values ( 5 , 'BR' )
insert into #Seminar values ( 6 , 'BR' )

insert into #Registrant values ( 1 , 1 )
insert into #Registrant values ( 2 , 2 )
insert into #Registrant values ( 3 , 1 )
insert into #Registrant values ( 4 , 3 )
insert into #Registrant values ( 5 , 1 )
insert into #Registrant values ( 6 , 2 )
insert into #Registrant values ( 7 , 4 )
insert into #Registrant values ( 8 , 2 )
insert into #Registrant values ( 9 , 3 )
insert into #Registrant values ( 10 , 1 )

--The sample data:
SELECT *
FROM #Topic AS t
JOIN #Seminar AS s
    ON s.TopicID = t.Topicid
JOIN #Registrant r
    ON r.seminarid = s.seminarid
ORDER BY t.Topicid, s.seminarid , r.registrantid
/*
TopicID TopicName            SeminarID TopicID RegistrantID SeminarID
------- -------------------------------- ----------- ------- ------------ -----------
AC   Advanced Coding              3 AC         4      3
AC   Advanced Coding              3 AC         9      3
AC   Advanced Coding              4 AC         7      4
BR   How to be the Best Receptionist      1 BR         1      1
BR   How to be the Best Receptionist      1 BR         3      1
BR   How to be the Best Receptionist      1 BR         5      1
BR   How to be the Best Receptionist      1 BR        10      1
IP   Insurance Processing            2 IP         2      2
IP   Insurance Processing            2 IP         6      2
IP   Insurance Processing            2 IP         8      2
*/

-- Problem: To show each topic , distinct seminars in each topic &
--     the distinct registrations in each topic.
SELECT t.TopicID ,
    t.TopicName ,
    ( SELECT COUNT( DISTINCT s.SeminarID )
     FROM #Seminar AS s
     WHERE s.TopicID = t.TopicID ) AS Seminars ,
    ( SELECT COUNT( DISTINCT r.RegistrantID )
     FROM #Registrant AS r
     JOIN #Seminar AS s
     ON r.SeminarID = s.SeminarID
     WHERE s.TopicID = t.TopicID ) AS Registrants
FROM #Topic AS t
ORDER BY t.TopicID
/*
TopicID TopicName            Seminars  Registrants
------- -------------------------------- ----------- -----------
AC   Advanced Coding              2      3
BR   How to be the Best Receptionist      3      4
IP   Insurance Processing            1      3
*/