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