1

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
forpas
  • 160,666
  • 10
  • 38
  • 76
Mark
  • 3,653
  • 10
  • 30
  • 62

1 Answers1

0

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thank you! I made a mistake in that I didnt include an important point in the original question, I made a new question here: https://stackoverflow.com/questions/72562010/is-it-possible-to-select-rows-where-the-occurences-of-an-id-is-some-value-or-t – Mark Jun 09 '22 at 14:22