How can I filter an SQL table to show only keys with exactly N entries?

Question

Lets say I have a table with a column named KEY.

I want to find all KEYs which are in the table exactly 3 times. How can I do that?

I managed to get a list of how many entries I have for each KEY, like this:

select count(*) from my_table group by KEY;

but how can I filter it to show only those who have the value 3?

select count(*) from my_table group by KEY having count(*) =3 — mhn, Sep 24 '14 at 13:53

score 3 · Accepted Answer · answered Sep 24 '14 at 13:53

3

select KEY 
from my_table 
group by KEY 
having count(*) = 3

answered Sep 24 '14 at 13:53

GolezTrol

114,394
18
182
210

1

See [here](http://stackoverflow.com/questions/287474/whats-the-difference-between-having-and-where) for more detail on the `HAVING` clause ... – AHiggins Sep 24 '14 at 14:10

fancyPants · Answer 2 · 2014-09-24T14:16:13.213

1

The having clause filters after grouping (where filters before).

select `key`
from my_table 
group by `KEY`
having count(*) = 3;

edited Sep 24 '14 at 14:16

answered Sep 24 '14 at 13:53

fancyPants

50,732
33
89
96

score 0 · Answer 3 · edited Sep 24 '14 at 14:29

0

select KEY 
from my_table 
group by KEY 
having count(1) = 3

edited Sep 24 '14 at 14:29

AHiggins

7,029
6
36
54

answered Sep 24 '14 at 14:03

Shawpnendu

21
2

Shawpnendu, check out [this question here](http://stackoverflow.com/questions/1221559/count-vs-count1) - there actually isn't a difference between `COUNT(*)` and `COUNT(1)` in modern versions of SQL Server, so this answer is actually a duplicate of the one GolezTrol posted nine minutes before yours. – AHiggins Sep 24 '14 at 14:31

score 0 · Answer 4 · edited Jun 20 '20 at 09:12

Try with Row Number concept

;
WITH Temp_tab AS
( SELECT '1' Key_,'az' Key_Value
UNION SELECT '1' ,'a5'
UNION SELECT '1' ,'a6'
UNION SELECT '2' ,'a1'
UNION SELECT '3' ,'a2'
UNION SELECT '4' ,'a3'
UNION SELECT '1' ,'a4'
UNION SELECT '3' ,'a21'
UNION SELECT '3' ,'a22'),
  Tab2 AS
(SELECT *, ROW_NUMBER() over(partition BY key_ ORDER BY key_) count_ FROM Temp_Tab)
SELECT key_    
FROM tab2 WHERE count_ = 3

code for your table

;with temp_table
(select *,ROW_NUMBER() over(partition by key_ order by key_) count_ from my_table)
select key_ from temp_table where count_ = 3

How can I filter an SQL table to show only keys with exactly N entries?

4 Answers4

Try with Row Number concept

code for your table