SQL Filter by count of records

Question

I have a table of the form: (Answer must work for PostGreSQL 9.2

StateName, ContryName, Pop, etc. // btw state may also be province, I use them interchangeabley

I would like to remove any country that has too many states

This does a filter, but since I am grouping by the country (states.admin) I get an error because states.name is not in the group clause.

I want a filtered table, that just removes all rows for a country that more than 100 states.

Am I making sense? I assume I need some sort of WHERE subquery.

SELECT 
  states.name,
  states.admin
FROM
  vector.states
GROUP BY 
  states.admin
HAVING COUNT(*) < 100
ORDER BY
  states.admin ASC;

Answer 1

SELECT s.name,s.admin 
FROM states s
INNER JOIN (
SELECT ss.admin
FROM states ss
GROUP BY ss.admin
HAVING COUNT(*) < 100
) a ON a.admin = s.admin
ORDER BY s.admin ASC;

sqlfiddle demo