Sql query with count per row

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Here is my table structure

ID   Name      Phone_No 
1    ABC        123
2    ABC        123
3    XYZ        222
4    PQR       444

and required output is like this; Here is i want to add another colunm which gives count as per Phone No

XML

ID   Name      Phone_No   Count
1    ABC        123         2
2    ABC        123         2
3    XYZ        222         1
4    PQR        444         1

So plz help me how to write sql query

Posted 10-Jan-13 2:19am

AshishSarang

Add a Solution

5 solutions

Solution 3

Hi,

you can first make the Scalar valued function which returns the count.

SQL

CREATE FUNCTION fn_GetPhoneCount 
(
	@Phone_No nvarchar(50) = ''
)
RETURNS int
AS
BEGIN

	Declare @Cnt INT
	SET @Cnt = 0

	Select @Cnt = COUNT(*) from Table_1 where Phone_No = @Phone_No

	Return @Cnt
END

Then write the query like this.

SQL

Select [ID], [Name], [Phone_No], dbo.fn_GetPhoneCount([Phone_No]) AS [Count] from Table_1

I hope this will help you to get your answer.

Posted 10-Jan-13 23:02pm

VIPR@T

Comments

AshishSarang 11-Jan-13 5:35am

Thanks...!!

Solution 4

Look at here ... How do u like that...:)

SQL

SELECT T11.ID , T11.Name , T11.Phone_No , COUNT(T11.Phone_No)  FROM T11 (NOLOCK)
INNER JOIN T11 as B (NOLOCK)
    ON B.Name = T11.Name
GROUP BY T11.ID , T11.Name , T11.Phone_No

Regards,
Vijay

Posted 13-Jan-13 17:49pm

P.S Vijay

Updated 13-Jan-13 17:51pm

v2

Solution 5

Try this:

Select ID,Name,Phone_no, Count(Phone_no)
From TableName
GroupBy ID,Name,Phone_no

Posted 13-Jan-13 18:45pm

Vishal.Shimpi144

Solution 2

Select T1.ID, T1.Name, T1.Phone_no, T2.Count From myTable T1, (Select Phone_no, Count(Phone_No) as Count From myTable) as T2 Where T1.Phone_no = T2.Phone_no

Posted 10-Jan-13 22:16pm

AshishSarang

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)

**Zoltán Zörgő** · Accepted Answer · 2013-01-10T02:37:00

Solution 1

Try this one:

SQL

select t1.ID, t1.Name, t1.Phone_No, (select count(*) from mytable t2 where t2.Name=t1.Name and t2.Phone_No=t1.Phone_No) as Count from mytable t1

Posted 10-Jan-13 2:37am

Zoltán Zörgő