CODEWITHSUNDEEP
sqlsql-server
Selva
Selva

Reputation: 7

query to find more than one name with different values

this is my table i need more than two names will appear as out put i used count in my query, but name timur has diff company so it cant count as 1 i need count as 2

Name    ID  Company Name    CompanyID   Role Name
Ahmed   73  King & Spalding     55      Counsel
Timur   78  Chance CIS Ltd     39       Partner
Timur   78  Clifford LLP       28       Counsel
Rahail  80  Reed Smith ltd     97       Partner

out put like this

Name    ID  Company Name    CompanyID   Role Name  count
Ahmed   73  King & Spalding     55      Counsel     1
Timur   78  Chance CIS Ltd     39       Partner     2
Timur   78  Clifford LLP       28       Counsel     2
Rahail  80  Reed Smith ltd     97       Partner     1

Upvotes: 0

Views: 327

Answers (3)

Tom H
Tom H

Reputation: 47464

If you're using SQL Server 2005 or above then you can use a window function to achieve this easily:

SELECT
    T.Name,
    T.ID,
    T.CompanyName,
    T.CompanyID,
    T.RoleName,
    COUNT(*) OVER (PARTITION BY T.Name)
FROM
    My_Table T

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

I am assuming that name and ID match each other. So in case of duplicated names for different people, I am using ID for partitioning

SELECT 
  *, 
  count(*) over (partition by ID) as [count]
FROM yourtable

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Use correlated sub-query:

select t.*, (select count(*) from tablename where name = t.name) as count
from tablename t

Upvotes: 0

Related Questions