I have the first three columns in SQL. I want to create the 4th column called Count which counts the number of times each unique name appears in the Name column. I want my results to appears like the dataset below, so I don't want to do a COUNT and GROUP BY.

What is the best way to achieve this?

CodePudding user response：

We can try to use COUNT window function

SELECT *,COUNT(*) OVER(PARTITION BY name ORDER BY year,month) count
FROM T 
ORDER BY year,month

sqlfiddle