How can I get a count for duplicates in SQL Server and write the result for each row to a column (without excluding rows)?

Suppose I have a table, like this:

MyTable

Name     | Number
-----------------
John     |   3
Jacob    |   2
Mark     |   5
John     |   62
Kathy    |   1
John     |   1
Alex     |   38
Mark     |   44

How can I get the number of occurrences a name appears in a given column, and then display that number to new column?

For example, this would be my desired output:

Name     | Number  | NoEntries
------------------------------
John     |   3     |     3
Jacob    |   2     |     1
Mark     |   5     |     2
John     |   62    |     3
Kathy    |   1     |     1
John     |   1     |     3
Alex     |   38    |     1
Mark     |   44    |     2

Instinctively, I want to use something like

SELECT Name, COUNT(Name) AS NoEntries
FROM MyTable
GROUPBY Name;

However, this not only omits the Number column, but it also doesn’t return ALL the results. I want to list all the results back, without excluding any rows, and then simply put a column with the number of times Name appears in the table.

Searching stackoverflow (and google), I found some of these results:

Count duplicate values in SQL Server and display as another column

Count Duplicate Data in Column and Display Once Per Row – SQL

Finding duplicate values in a SQL table

The problem with these links and everything I am finding online is that they are all using GROUP BY. Is there a simple, clean way to get the count of each name in the table, and then write the number to a column for each row?

Answer

Maybe you simply need a windowed COUNT():

SELECT Name, Number, COUNT(Name) OVER (PARTITION BY Name) AS NoEntries
FROM MyTable

Advertisement

Answer