Search code examples
mysqlcountsubquery

SELECT id WHERE COUNT(*) > X ? - How to get records from any user that has more than X records in table?

Obviously the query in the title does not work, but it might illustrate in a naive way, what I would like to do. I have a table that contains some users identified by an id column. This id is NOT unique within the database. It marks a user that may have multiple records in my table.

How can I show the whole record of all users (identified by id) that have more than 10 records in my table?

Solution

  • Use having instead of where:

    SELECT id
  FROM (
        SELECT id, COUNT(*) as cnt
        FROM somewhere 
        GROUP BY id
        HAVING cnt > 1
   ) temp_table