CODEWITHSUNDEEP
sqlcountrow-numberdistinct-values
João Marcelo Costa
João Marcelo Costa

Reputation: 3

count number of times a value appears in a column not GROUP BY in SQL

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!

Upvotes: 0

Views: 957

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use a window function . . . but count(*):

select count(*) over (partition by name, city) as countx

Upvotes: 1

Related Questions