Using Count and Max in the same query

[shauns1]

Hi

Here is the structure of my tblSessions:

SessionID (auto number)
DevelopmentID (number)
CreationDate (DateTime)

Is there a query that would count all of the sessions for each development and return the DevelopmentID that has the highest number of Sessions.

Similarly, I would like to be able to return the DevelopmentID with the lowest number of sessions too.

Many thanks for your time.

Shaun

 

[PHV]

Something like this ?
SELECT DevelopmentID, COUNT(*) NumberOfSessions
FROM tblSessions
GROUP BY DevelopmentID
HAVING COUNT(*) = (SELECT MAX(NumberOfSessions) FROM (SELECT DevelopmentID, COUNT(*) NumberOfSessions
FROM tblSessions
GROUP BY DevelopmentID))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shauns1]

Hi

Thanks!

Yes this works fine.

Thank you for your time...