Let us assume one has the following table (without the CountX column) :
| **ID** | **Name** | **City** | **CountX** | | 1 | Ana | London | 1 | | 2 | Ana | Paris | 1 | | 3 | Mary | Paris | 2 | | 4 | Mary | Paris | 2 | | 5 | John | London | 2 | | 6 | John | London | 2 |
I would like to add the column ‘CountX’ with the number of different entries, per each combination of Name & City.
I tried to use ROW_NUMBER() but it does not work well:
ROW_NUMBER () OVER (PARTITION BY Name, City ORDER BY Name) AS CountX
I also tried to do a sub-query, using select distinct Name from table GROUP BY Name
but could not
Thank you in advance!
Advertisement
Answer
Use a window function . . . but count(*)
:
select count(*) over (partition by name, city) as countx