 |
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

May 18th, 2006, 08:32 AM
|
Registered User
|
|
Join Date: May 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Query Help, Please!
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?
|

May 30th, 2006, 06:50 AM
|
Authorized User
|
|
Join Date: May 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
How about something like this
SELECT COUNT(ProgramParticipantID) FROM [Program Participants] Where ProgramID =4
|

May 30th, 2006, 06:53 AM
|
Registered User
|
|
Join Date: May 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

May 30th, 2006, 08:20 AM
|
Authorized User
|
|
Join Date: May 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 30th, 2006, 08:25 AM
|
Registered User
|
|
Join Date: May 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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!
|

May 30th, 2006, 08:53 AM
|
Authorized User
|
|
Join Date: May 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 31st, 2006, 08:14 AM
|
Registered User
|
|
Join Date: May 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

May 31st, 2006, 09:11 AM
|
Authorized User
|
|
Join Date: May 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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;
|
|
 |