按要选择的值分组，并仅采用记录数超过20条的组

SELECT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5) 
FROM table s
WHERE CITY IN (...)
AND STATE IN (...)
GROUP BY s.CITY, s.STATE, SUBSTR(s.ZIP,1,5) 
HAVING count(*) > 20

这是使用子查询的另一种方法

SELECT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5)
FROM Table S JOIN
(
  SELECT ID, COUNT(ID) Cnt
  FROM Table
  GROUP BY ID
) SS
ON S.ID = SS.ID
WHERE CITY IN (...)
AND STATE IN (...)
AND Cnt > 20

如果需要对聚合进行过滤，请使用HAVING。HAVING在分组后应用，如下所示

SELECT DISTINCT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5), count(s.id) as count FROM table s
WHERE CITY IN (...)
AND STATE IN (...) 
GROUP BY s.CITY, s.STATE, SUBSTR(s.ZIP,1,5)
HAVING count > 20;

您可以查看HAVING子句：https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html
我不太确定你的数据会怎么样。

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

显然，您不需要按分组，因为您选择的是区分...所以可能是这样的东西？

SELECT DISTINCT s.CITY, s.STATE, SUBSTR(s.ZIP,1,5), COUNT(s.ID) FROM table s HAVING COUNT(s.ID) > 20

希望这有帮助！