On SQL I want to filter a query by the amount of times it appears in a table

Question

I'm working on SQL and I'm trying to find rows where the values are the same

If I have a table like this

|Column1|
| a |
| a |
| b |
| c |
| d |
| e |

I want to run a query that shows me each value that repeats more than once.

Select * from table 
Where ***the total amount of occurrences of the value in Column1*** >= 2

and it should bring me back just the first and second rows where Column1 = a

Duplicate of [SQL query for finding records where count > 1](https://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1). — Tim Biegeleisen, May 25 '21 at 11:41

score 2 · Answer 1 · answered May 25 '21 at 11:36

You can use aggregation:

select column1
from t
group by column1
having count(*) > 1;

You can include the count in the select as well, to see the number of times a value appears.

If you want the entire row (that is, there are other columns), then use window functions:

select t.*
from (select t.*, count(*) over (partition by column1) as cnt
      from t
    ) t
where cnt > 1
order by column1;

Prateek Chaudhary · Answer 2 · 2021-05-25T11:43:17.850

0

SELECT
    Column1 , COUNT(*)
FROM
    table
GROUP BY
    Column1 
HAVING 
    COUNT(*) > 1

If you want to add condition then it use where condition

 SELECT
    Column1 , COUNT(*)
FROM
    table where Column1 = 'a'
GROUP BY
    Column1 
HAVING 
    COUNT(*) > 1

edited May 25 '21 at 11:43

answered May 25 '21 at 11:36

Prateek Chaudhary

2 Answers2