Geek Answers Handbook

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

Obviously, the request in the header does not work, but it can naively illustrate what I would like to do. I have a table containing some users identified by the id column. This identifier is NOT unique in the database. It marks a user who may have multiple entries in my table.

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

+5
source share
4 answers

Use with instead , where :

SELECT id
  FROM (
        SELECT id, COUNT(*) as cnt
        FROM sowewhere 
        GROUP BY id
        HAVING cnt > 1
   ) temp_table
+5
source 
SELECT * FROM user 
WHERE id IN (SELECT id FROM user GROUP BY id HAVING COUNT(*) > 10)
+2
source 
SELECT id, COUNT(*) FROM Table GROUP BY id HAVING COUNT(*) > 10
+1
source

here's how:

 CREATE TABLE mytable_clean AS
 SELECT * FROM mytable WHERE id IN(
 SELECT id FROM
 (SELECT id,COUNT(*) AS appearance 
 FROM mytable
 GROUP BY id) AS id_count
 WHERE id_count.appearance > 9 )

He works. It doesn't slow down, but it looks a bit awkward for me. Best solutions are welcome :)

0
source

More articles:


All Articles