Problem modifying a simple query into a subquery 1

[jq65]

The following simple query executes ok

select location_name, count(*)
from observation
where season (observation_date) = 'summer'
group by location_name;

Season is a function and the query returns the locations and number of occurences of a certain activity
I now want to modify it into a composite query that gives the name of the location with the largest number of observations in summer.

I am stumped with the logic

Any assistance would be gratefully appreciated

Thanks

 

[grega]

Something like this should work ...

select location_name, count(*)
from observation
where season (observation_date) = 'summer'
group by location_name
having count(*) = (select max(count(*))
from observation
where season (observation_date) = 'summer'
group by location_name)

Greg.

 

[jq65]

Thanks for the suggestion Greg but this returns an error : "Subquery cannot return more than one result"
Also I only want the location with the maximum occurences

 

[jq65]

Greg

Thanks - I modified your code a little and it works fine