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
CodePudding user response：
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.