Click here to Skip to main content
12,512,130 members (53,571 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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
Updated 8-Nov-12 20:32pm
Maciej Los237.7K
v4
Comments
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: bad
 
good
Please Sign up or sign in to vote.

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!
:)
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160929.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100