Filter by number of occurrences in a SQL Table

Question

Given the following table where the Name value might be repeated in multiple rows:

How can we determine how many times a Name value exists in the table and can we filter on names that have a specific number of occurrances.

For instance, how can I filter this table to show only names that appear twice?

Answer 1

You can use group by and having to exhibit names that appear twice in the table:

select name, count(*) cnt
from mytable
group by name
having count(*) = 2

Then if you want the overall count of names that appear twice, you can add another level of aggregation:

select count(*) cnt
from (
    select name
    from mytable
    group by name
    having count(*) = 2
) t

Answer 2

You need to use a GROUP BY clause to find counts of name repeated as

select name, count(*) AS Repeated
from Your_Table_Name
group by name;

If You want to show only those Which are repeated more than one times. Then use the below query which will show those occurrences which are there more than one times.

select name, count(*) AS Repeated
from Your_Table_Name
group by name having count(*) > 1; 

Answer 3

It sounds like you're looking for a histogram of the frequency of name counts. Something like this

with counts_cte(name, cnt) as (
    select name, count(*) 
    from mytable
    group by name)
select cnt, count(*) num_names
from counts_cte
group by cnt
order by 2 desc;