0

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?

nightmare637
  • 635
  • 5
  • 19

1 Answers1

2

Maybe you simply need a windowed COUNT():

SELECT Name, Number, COUNT(Name) OVER (PARTITION BY Name) AS NoEntries
FROM MyTable
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • Even before you posted the solution, your hint was already a HUGE help! It led me to this resource: https://www.sqlshack.com/use-window-functions-sql-server/ However, I didn't even finish the article before I saw you had updated your comment to be an answer. It worked out perfectly too! Thank you so much for the quick response; you have completely resolved my issue! – nightmare637 Jan 19 '22 at 17:46