CODEWITHSUNDEEP
sql
Solomiia Kalinchuk
Solomiia Kalinchuk

Reputation: 193

Count duplicated column data

I have the data like that:

UserId   LocationId   ProjectId
  1         123          1234
  1         323          1234
  2         213          1234
  3         234          1234
  1         123          2345
  1         323          2345
  2         213          2345
  3         234          2345

I need to show data of UserIds that are duplicated in a ProjectId using column Count

    UserId   LocationId   ProjectId  Count
      1         123          1234      2
      1         323          1234      2
      2         213          1234      1
      3         234          1234      1
      1         123          2345      2
      1         323          2345      2
      2         213          2345      1
      3         234          2345      1

Will be glad for any help)

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In most databases, you would solve this most succinctly and efficiently using ANSI standard window functions:

select t.*, count(*) over (partition by projectid, userid) as usercount
from table t;

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Something like this should work:

 Select UserId   
        LocationId  
        ProjectId,
       (Select Count(*) From TableName t2 Where t2.UserId = t1.UserID And t2.ProjectId = t1.ProjectId) As Count
 From TableName t1

Upvotes: 2

Related Questions