SQL Query To Obtain Value that Occurs more than once

Question

I need to query my database to show the records inside my table where lastname occurs more than three times  Example  in my Students Table  there are 3 people with Lastname  Smith   4 with  Johnson   and 1 with  Potter   My query should show the records of those with the lastnames Smith  and Johnson since these values occur more than or equal to 3 times   Can anyone point me to this  I was thinking of using COUNT   but I can t seem to think how to apply it

User · Answer

From Oracle  but works in most SQL DBs    SELECT LASTNAME  COUNT    FROM STUDENTS GROUP BY LASTNAME HAVING COUNT     gt   3   P S  it s faster one  because you have no Select withing Select methods here

User · Answer

For postgresql   SELECT   AS rec  FROM       SELECT lastname  COUNT    AS counter      FROM students      GROUP BY lastname  AS tbl  WHERE counter  gt  1

User · Answer

For MySQL   SELECT lastname AS ln      FROM       SELECT lastname  count    as Counter       FROM  students        GROUP BY  lastname   AS tbl WHERE Counter  gt  2

User · Answer

For SQL Server 2005    WITH T AS   SELECT            COUNT    OVER  PARTITION BY Lastname  as Cnt FROM Students   SELECT     TODO  Add column list  Don t use                          FROM T WHERE Cnt  gt   3

User · Answer

SELECT LASTNAME  COUNT    FROM STUDENTS GROUP BY LASTNAME ORDER BY COUNT    DESC

User · Answer

The answers mentioned here is quite elegant https   stackoverflow com a 6095776 1869562 but upon testing  I realize it only returns the last name  What if you want to return the entire record itself   Do this  For Mysql   SELECT   FROM  beneficiary  WHERE  lastname  IN      SELECT  lastname    FROM  beneficiary    GROUP BY  lastname    HAVING COUNT   lastname     gt 1

User · Answer

I think this answer can also work  it may require a little bit of modification though     SELECT   FROM Students AS S1 WHERE EXISTS SELECT Lastname  count    FROM Students AS S2 GROUP BY Lastname HAVING COUNT     gt  3 WHERE S2 Lastname   S1 Lastname

[sql] SQL Query To Obtain Value that Occurs more than once

Examples related to sql