I think what you need to see is the COUNT(DISTINCT NameOfAthlete) which
only counts each identical name as 1, no matter how many results there are.
SELECT SeasonCCYY, COUNT(DISTINCT NameOfAthlete) +
FROM IndividualResults +
WHERE ETInSeconds > 0 +
GROUP BY SeasonCCYY +
ORDER BY 2 Desc +
Albert
On 5/28/2014 4:10 PM, Wills, Steve wrote:
Zeb, I probably should have simplified this; I just took a previously
existing query and tested it, then pasted it, but, related to what I
think your need is, the following might have been better:
SELECT SeasonCCYY,
COUNT(NameOfAthlete)
FROM INDIVIDUAL_RESULTS
WHERE ETInSeconds <> 0
GROUP BY
SeasonCCYY
ORDER BY
2 DESC
Translation:
Get the year,
total number of runners
from individual performance records
where the runner crossed the finish line
grouped by
the year
ordered by
how many finishers during the season from most to least
*From:*[email protected] [mailto:[email protected]] *On Behalf Of
*Wills, Steve
*Sent:* May 28, 2014 4:38 PM
*To:* RBASE-L Mailing List
*Subject:* [RBASE-L] - Re: Date Where Max(CaseCount) Occurs
Zeb (and Bill), what about something like this:
SELECT SeasonCCYY,
NameOfSchoolCD,
COUNT(NameOfAthlete)
FROM INDIVIDUAL_RESULTS
WHERE ETInSeconds <> 0
AND NameOfSchoolCD IN ('SAA','SDS')
GROUP BY
SeasonCCYY,
NameOfSchoolCD,
ORDER BY
2,
3 DESC
Translation:
Get the season,
name of the school
how many runner names
from individual results table
where performance/elapsed time has a positive integer value
and name of the school is for my girls and my boys teams
group by
the season,
name of the school
order by
name of the school (to keep boys and girls
counts together)
how many runner names from most to least (IOW, which year’s team has
had the most runners)
*From:*[email protected] <mailto:[email protected]>
[mailto:[email protected]] *On Behalf Of *Bill Downall
*Sent:* May 28, 2014 4:23 PM
*To:* RBASE-L Mailing List
*Subject:* [RBASE-L] - Re: Date Where Max(CaseCount) Occurs
On Wed, May 28, 2014 at 5:18 PM, A. Razzak Memon <[email protected]
<mailto:[email protected]>> wrote:
It currently takes me two steps to answer this question: (1)
SELECT MAX(CaseCount) FROM GroupCaseCountByYear; (2) SELECT
CaseYear WHERE
CaseCount = #. (#--is the number I got from step (1)) This seems
counter
productive.
Zeb,
You can combine your two queries into one:
SELECT CaseYear FROM GroupCaseCountByYear +
WHERE CaseCount = (SELECT MAX (CaseCount) FROM GroupCaseCountByYear)
