using COUNT () with separate table in query

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

SQL Server 2000 Forums

SQL Server Development (2000)

using COUNT () with separate table in query

Author

Topic

Linok
Starting Member

34 Posts

Posted - 2007-10-30 : 14:38:21

Hey Everyone,

First off, I apologize if this has already been asked, but I couldn't find it in the search.

I've got 2 tables Workshops and Attendees, I'm trying to create a query that pulls a COUNT of Attendees whenever I pull the Workshops data.

For example:

SELECT Id, Presenter, Code, Title, StartDate, EndDate, Loc_name, COUNT(Attendees of this workshop)
FROM Workshops

Any suggestions?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-30 : 14:42:56

You would need to provide the common link between the two tables

SELECT a.Id, Presenter, Code, Title, StartDate, EndDate, Loc_name, COUNT(Attendees)
FROM Workshops a inner join Attendees b on a.ID = b.ID
GROUP BY Id, Presenter, Code, Title, StartDate, EndDate, Loc_name

Assuming the workshops table only has 1 row per ID and the ID column links to the attendees table and providing the attendees table links on the ID column of the workshops table.

Linok
Starting Member

34 Posts

Posted - 2007-10-30 : 14:53:12

Works perfectly!

Thanks alot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources