What results are you getting?
Take a look at the DISTINCT COUNT function, instead of the COUNT function.
This should take care of the same eventID in different rodeos.
-----Original Message-----
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 2: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
