Hi there,

I'm querying a single table with the goal of selecting the number of
distinct cities of the field 'city' where the count is greater than 9
and ordering by the result by the count. The closest I've been able to
come is this:

'select city,count(1) as list from residential group by city order by
list desc;'

Which will order the full list. Inserting a where statement "where
list > 10" seems to keep resulting in a syntax error. Is it possible
to limit my results in the sql statement, or should I do it in my
programming logic instead?

salvador wrote:
If you want to restrict the search on a condition involving the result of an aggregate function you must use the HAVING
clause instead of the WHERE clause.

select
city, count(*) as list
from
residential
group by
city
having
list > 10
order by
list desc;

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.org/

salvador wrote:
Something like:

SELECT city, count( * ) AS cnt
FROM residential
GROUP BY city
having cnt > 9
order by cnt desc

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Hey Jerry and Giuseppe,

Thanks a bunch for your help. This worked great!.

-Sal

Giuseppe Maxia wrote: