Albert, you are absolutely correct and, to be honest, that is what I had in the original query. It seemed to me that Zeb might be a bit new with some of this, so I refrained from putting that in my examples. Also, extending what you said, I often use both COUNT(*) and COUNT(DISTINCT whatever_column) to look for dup’s/lack of good relational integrity/whatever, especially when I am working with data that is new or unfamiliar to me.
Hey, is there anything out there that is some sort of “data-value-profiler”? If not, maybe one of us should create one ... wait, who has the time? ;-) Steve From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry Sent: May 28, 2014 7:12 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Date Where Max(CaseCount) Occurs 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]> [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)
