-2

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.

sashoalm
  • 69,127
  • 105
  • 396
  • 720
  • 1
    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

3 Answers3

2

You could use Group by + Having clause:

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

DEMO

Tim Schmelter
  • 429,027
  • 67
  • 649
  • 891
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
shauryachats
  • 9,275
  • 4
  • 34
  • 47
-1

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

SQL FIDDLE

Rafay
  • 573
  • 1
  • 6
  • 23