I would like to find out the number of participants that we have for each site ID in our database, and also find the number of people who completed the program at each site (both for this year, ProgramID=4). My two tables look like this (linked by ProgramParticipantID):

Program Participants
Site ID ProgramParticipantID ProgramID
831 1 4
831 4 4
879 246 4
879 149 4
879 649 4

ParticipantLogs
ProgramParticipantID 4DaysMet 5DaysMet
1 0 0
4 1 0
249 0 1
149 1 0
649 0 0

You'll notice that in the ParticipantLogs table, a participant can only complete 4daysMet or 5DaysMet (or they could not have completed the program at all, so both would equal zero).

Can anyone help?

How about something like this
SELECT COUNT(ProgramParticipantID) FROM [Program Participants] Where ProgramID =4

Thanks for replying! That will count all Participants for this year, whereas I want the number of participants for each individual site. In the example above, Site ID 831 has two and 879 has three. Any ideas?

I don't have access to a DB right now, so I'm sorry if the syntax is a little off. Let me know if this is what you are looking for.

SELECT [Site ID], COUNT(ProgramParticipantID)
FROM [Program Participants]
GROUP BY [Site ID], ProgramParticipantID
HAVING [Site ID]=831

So you're saying that there's basically no way for me to pass the changing siteid's to the query, which is what I thought. I'd have to change the wording of every query to reflect the site id that I'm looking for, which isn't worth my time....there's thousands of site id's.
Thank you for your help, though!

Are you using an MS SQL Server?

If so you could for example create a Stored Procedure and then pass the SiteID to the SP as a variable.

CREATE procedure sp_SiteSummary
@VariableSiteID int
AS
SELECT [Site ID], COUNT(ProgramParticipantID)
FROM [Program Participants]
GROUP BY [Site ID], ProgramParticipantID
HAVING [Site ID]= @VariableSiteID

GO

So to count the number of Participants per site, I've created the following:

CREATE PROCEDURE sp_ParticipantsPerSite
@VariableSiteID int
AS
SELECT SiteID, COUNT(ProgramParticipantID)
FROM ProgramParticipants
WHERE (ProgramID=4) AND (SiteID > 0)
GROUP BY SiteID
HAVING SiteID=@VariableSiteID

GO

How do I execute the Stored Procedure, passing in the SiteID?

You can set up a loop structure to loop through the site ID's and then pass each ID to the SP.

EXEC sp_ParticipantsPerSite @VariableSiteID = 1;