Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 18th, 2006, 08:32 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?
Reply With Quote
  #2 (permalink)  
Old May 30th, 2006, 06:50 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #3 (permalink)  
Old May 30th, 2006, 06:53 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #4 (permalink)  
Old May 30th, 2006, 08:20 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #5 (permalink)  
Old May 30th, 2006, 08:25 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

Reply With Quote
  #6 (permalink)  
Old May 30th, 2006, 08:53 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #7 (permalink)  
Old May 31st, 2006, 08:14 AM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #8 (permalink)  
Old May 31st, 2006, 09:11 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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;



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM



All times are GMT -4. The time now is 06:55 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.