Find values repeated exactly N times in column

Question

How can I find the values in an SQL column that are repeated exactly N times? For example, let's say I have a column that has the values fanta, sprite, sprite, sprite, cola, cola, cola, cabbages, cabbages, cabbages, cabbages, and I want to find the values which is repeated exactly 3 times, which should return sprite and cola, but not fanta or cabbages, because they are not repeated exactly 3 times.

I tried to adapt the SQL from Find most frequent value in SQL column:

SELECT value1, COUNT(value1) AS value1_count
FROM table1
GROUP BY value1
WHERE value1_count=3

But that gives me near "WHERE": syntax error:, so obviously I'm not doing it right.

Edit: Finding duplicate values in a SQL table was suggested as exact duplicate, but it is about finding all repeated values, regardless of how many times they are repeated, though I admit the answers there make it obvious how to reach a solution in my case, so I don't know.

possible duplicate of [Finding duplicate values in a SQL table](http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Klas Lindbäck, Mar 24 '15 at 08:28

score 2 · Accepted Answer · answered Mar 24 '15 at 08:25

2

You could use Group by + Having clause:

SELECT value1, COUNT(value1) AS value1_count
FROM table1
GROUP BY value1
HAVING COUNT(*) = 3

DEMO

answered Mar 24 '15 at 08:25

Tim Schmelter

450,073
74
686
939

score 2 · Answer 2 · answered Mar 24 '15 at 08:26

2

To apply a condition on GROUP BY, use HAVING.

Try this:

SELECT value1, COUNT(value1) AS value1_count FROM table1
GROUP BY value1
HAVING COUNT(value1) = 3

answered Mar 24 '15 at 08:26

shauryachats

9,975
4
35
48

score -1 · Answer 3 · answered Mar 24 '15 at 08:36

-1

You have to use HAVING clause with aggregate functions .. Below is the example

SQL FIDDLE

answered Mar 24 '15 at 08:36

Rafay

603
2
9
24

Find values repeated exactly N times in column

3 Answers3