12,352,294 members (53,607 online)
Rate this:
See more:
Hi,

How to count the row number for the below table which is having a combination of same numbers when we are giving multiple values as input.
```@Member_ID = 39,51,---

ID                 Member_ID
18	         39,39,39,51,39
25	           51
97	           41
102	         41,51
113	         41,116
155	         39,217
888	         41,49,147,149,151,148,175,68
662	         202,202
841	         116,3,3
```
The above combination is generated by writing the following query -
```select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
select ID ,
(select convert(varchar, Applicant_Member_Id) + ','  as [text()] from Savings_Account_Applicant where ID =saa.ID  for xml path(''))  as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account;```
Posted 8-Nov-12 1:55am
Edited 8-Nov-12 20:32pm
Maciej Los221.3K
v4
Aarti Meswania 9-Nov-12 2:36am

that solution not worked?

and you was used count() then now why you want rownumber()?
Maciej Los 9-Nov-12 2:44am

What do you want to count: ID's or MemberID's or MemberID's for each ID?

Rate this:

## Solution 1

Apply row number as below
```select RowNo,Id,A_Ids from
(select Row_Number() over(order by Applicant_Member_Ids) as RowNo, ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) as A_Ids from
(
select ID ,
(select convert(varchar, Applicant_Member_Id) + ','  as [text()] from Savings_Account_Applicant where ID =saa.ID  for xml path(''))  as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account
) as temp order by RowNo;```
Happy Coding!
Rate this:

## Solution 2

First of all, see my comment to your post.

If you want to count ID's
```SELECT COUNT(ID) AS [CountOfID's]
FROM YourTable```
or
```SELECT COUNT(DISTINCT ID) AS [CountOfID's]
FROM YourTable```

Analogically, if you want to count MemebrID's
```SELECT COUNT(MemberID) AS [CountOfMemberID's]
FROM YourTable```
or
```SELECT COUNT(DISTINCT MemberID) AS [CountOfMemberID's]
FROM YourTable```

Finally, if you want to count MemberID's for each ID
```SELECT ID, COUNT(MemberID) AS [CountOfMemberID's]
FROM YourTable```
or
```SELECT ID, COUNT(DISTINCT MemberID) AS [CountOfMemberID's]
FROM YourTable```

Top Experts
Last 24hrsThis month
 OriginalGriff 564 Sergey Alexandrovich Kryukov 286 Dave Kreskowiak 270 Maciej Los 240 Karthik Bangalore 149
 OriginalGriff 9,304 Sergey Alexandrovich Kryukov 6,353 Dave Kreskowiak 3,174 ppolymorphe 2,256 Karthik Bangalore 2,251

Web02 | 2.8.160621.1 | Last Updated 9 Nov 2012