'Create SQL Column Counting Frequency of Value in other column

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?

enter image description here

postgresql


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih

Related Questions