how to count the row number for the having a combination of same numbers

Question

0.00/5 (No votes)

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 -

SQL

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 ali

Updated 8-Nov-12 20:32pm

Maciej Los

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Answer 1 · 2012-11-08T20:39:00

Apply row number as below

SQL

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!
:)

Maciej Los · Answer 2 · 2012-11-08T20:49:00

First of all, see my comment to your post.

If you want to count ID's

SQL

SELECT COUNT(ID) AS [CountOfID's]
FROM YourTable

or

SQL

SELECT COUNT(DISTINCT ID) AS [CountOfID's]
FROM YourTable

Analogically, if you want to count MemebrID's

SQL

SELECT COUNT(MemberID) AS [CountOfMemberID's]
FROM YourTable

or

SQL

SELECT COUNT(DISTINCT MemberID) AS [CountOfMemberID's]
FROM YourTable

Finally, if you want to count MemberID's for each ID

SQL

SELECT ID, COUNT(MemberID) AS [CountOfMemberID's]
FROM YourTable

or

SQL

SELECT ID, COUNT(DISTINCT MemberID) AS [CountOfMemberID's]
FROM YourTable

how to count the row number for the having a combination of same numbers

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0