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?

score 69 · Answer 1 · answered May 19 '14 at 10:51

69

From Oracle (but works in most SQL DBs):

SELECT LASTNAME, COUNT(*)
FROM STUDENTS
GROUP BY LASTNAME
HAVING COUNT(*) >= 3

P.S. it's faster one, because you have no Select withing Select methods here

answered May 19 '14 at 10:51

Andrii Kovalchuk

4,351
2
36
31

4

Great answer. It was generic enough to easily convert to the simple task I had at hand. – jerclarke Apr 09 '19 at 17:51
1

Worked perfectly for me in a BigQuery SQL request, really fast in addition ! – Alex Jan 11 '21 at 19:20
what if I want to return the duplicate records instead of grouping? – Peter Chaula Aug 04 '23 at 11:05

score 47 · Accepted Answer · answered May 23 '11 at 10:01

47

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 >= 3

answered May 23 '11 at 10:01

Martin Smith

438,706
87
741
845

1

works also for bigquery :) – Tomer S Jun 27 '22 at 11:10

score 14 · Answer 3 · edited May 29 '12 at 01:19

14

For MySQL:

SELECT lastname AS ln 
    FROM 
    (SELECT lastname, count(*) as Counter 
     FROM `students` 
     GROUP BY `lastname`) AS tbl WHERE Counter > 2

edited May 29 '12 at 01:19

gkrogers

8,126
3
29
36

answered May 23 '11 at 10:12

Till

3,084
17
18

score 11 · Answer 4 · answered Jun 05 '17 at 12:40

11

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` ) >1
)

answered Jun 05 '17 at 12:40

Kudehinbu Oluwaponle

1,045
11
11

1

It should also work in all DBMSs as it is ANSI SQL. – user1451111 Apr 12 '19 at 07:08

score 4 · Answer 5 · answered May 23 '11 at 10:08

4

SELECT LASTNAME, COUNT(*)
FROM STUDENTS
GROUP BY LASTNAME
ORDER BY COUNT(*) DESC

answered May 23 '11 at 10:08

garnertb

9,454
36
38

2

The OP wants "the records of those with the lastnames Smith, and Johnson" not just the last names themselves. – Martin Smith May 23 '11 at 10:09
Where is the part which tackles OP's requirement _"values occur more than or equal to 3 times"_. – user1451111 Apr 12 '19 at 07:06

score 3 · Answer 6 · edited Aug 01 '17 at 15:07

3

For postgresql:

SELECT * AS rec 
FROM (
    SELECT lastname, COUNT(*) AS counter 
    FROM students 
    GROUP BY lastname) AS tbl 
WHERE counter > 1;

edited Aug 01 '17 at 15:07

Hamza Abdaoui

2,029
4
23
36

answered Nov 25 '15 at 08:29

embuc

465
5
5

It should also work in all DBMSs as it is ANSI SQL. – user1451111 Apr 12 '19 at 07:09

Maryam · Answer 7 · 2020-02-17T09:50:16.167

-1

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(*) > 3 WHERE S2.Lastname = S1.Lastname)

edited Feb 17 '20 at 09:50

answered Feb 14 '20 at 19:10

Maryam

41
7

Please explain why your solution works for the OP's question. Link only answers are not considered good practice. Please see https://stackoverflow.com/help/how-to-answer – Marcello B. Feb 14 '20 at 19:31

SQL Query To Obtain Value that Occurs more than once

7 Answers7

Linked