Search code examples
mysqlgroup-bywhere-clausehavingsql-in

Is it possible to select rows where the occurences of an id is > some value?

Given a table:

userid activity location
1 RoomC 1
2 RoomB 1
2 RoomB 2
2 RoomC 4
3 RoomC 1
3 RoomC 5
3 RoomC 1
3 RoomC 5
4 RoomC 1
4 RoomC 5

Im trying to select only the rows where a userid shows up more then X number of times, lets say >2, so in the above database, only rows for userid 2 and 3 would be selected

Would something like this work?

SELECT *, count(*)
FROM marktable
GROUP BY userid
HAVING count(*) > 1
Solution

  • This modified version of your query:

    SELECT userid
FROM marktable
GROUP BY userid
HAVING COUNT(*) > 2

    returns all the users that appear more than 2 times in the table.

    Use it with the operator IN:

    SELECT *
FROM marktable
WHERE userid IN (
  SELECT userid
  FROM marktable
  GROUP BY userid
  HAVING COUNT(*) > 2
);

    See the demo.