Click here to Skip to main content
Search within:




Next

how to count the row number for the having a combination of same numbers
Sign Up to vote bad
good
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
sk. maqdoom ali670
Edited 8-Nov-12 20:32pm
Maciej Los60.3K
v4
Add a Solution

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?

2 solutions

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  
Posted 8-Nov-12 20:49pm
Maciej Los60.3K
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!
Smile | :)
  Permalink  
Posted 8-Nov-12 20:39pm
Aarti Meswania22K
Add a Solution

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Prasad_Kulkarni 452
1 Christian Graus 368
2 OriginalGriff 332
3 Ron Beyer 246
4 Prasad Khandekar 245
0 Sergey Alexandrovich Kryukov 7,061
1 Prasad_Kulkarni 4,046
2 OriginalGriff 3,749
3 _Amy 3,450
4 CPallini 3,114


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