would it be wise to try a sub query with this? or no? does anyone have any
other ideas or suggestions?
Thanks much,
Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
----- Original Message -----
From: "Bob Silverberg" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 1:47 PM
Subject: RE: Statement Help!!
> What happens if you change the COUNT(EventID) to COUNT(DISTINCT EventID)?
>
> Bob
>
> -----Original Message-----
> From: Jay Patton [mailto:[EMAIL PROTECTED]]
> Sent: March 28, 2001 3:09 PM
> To: CF-Talk
> Subject: Statement Help!!
>
>
> what i am trying to accomplish here is to show ONLY those contestants that
> have 2 or more DIFFERANT EventID's in the ResultsManager (table in my SQL7
> DB) (example: if they do the same event in every rodeo for the year they
> should NOT show up, but if they do 2 or more they should) the following is
> my query but something isnt working write and i cant seem to figure it
out.
> if anyone could help that would be great!
>
> my query:
>
> <cfquery name="GetTotalPts"
> datasource="#datasource#"
> dbtype="ODBC"
> username="#username#"
> password="#password#">
> SELECT ResultsManager.ContestantID, ResultsManager.EventID,
> ContestantManager.FirstName, ContestantManager.LastName,
> ContestantManager.City, ContestantManager.State, ContestantManager.Age,
SUM
> (ResultsManager.AdjPoints) as TOTALPTS,
> COUNT (EventID)
> FROM ResultsManager INNER JOIN ContestantManager ON
> ResultsManager.ContestantID = ContestantManager.ContestantID
> WHERE ContestantManager.Age < '51' AND ContestantManager.Gender = '1' AND
> ResultsManager.NotAllAround = '0'
> GROUP BY ResultsManager.ContestantID, ResultsManager.EventID,
> ContestantManager.FirstName, ContestantManager.LastName,
> ContestantManager.City, ContestantManager.State, ContestantManager.Age
> HAVING COUNT (EventID) >= 2
> ORDER BY SUM (AdjPoints) DESC
> </cfquery>
>
> Thanks in advance,
>
> Jay Patton
> Web Design / Application Design
> Web Pro USA
> p. 406.549.3337 ext. 203
> p. 1.888.5WEBPRO ext. 203
> e. [EMAIL PROTECTED]
> url. www.webpro-usa.com
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
