query like count(name),name,surname

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » query like count(name),name,surname

query like count(name),name,surname

Rate Topic

Display Mode

Topic Options

Author

Message

mayank jarsaniya

Posted Tuesday, May 19, 2009 1:24 AM

Old Hand

Group: General Forum Members
Last Login: Monday, December 26, 2011 1:28 PM
Points: 346, Visits: 401

I need query like
select count(name),name,surname from info

table info has only three columns
id int,
name varchar,
surname varchar

Post #719570

Samuel Vella

Posted Tuesday, May 19, 2009 1:30 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:22 AM
Points: 328, Visits: 1,852

select count(*), name, surname
from TableName
group by name, surname

Post #719571

mayank jarsaniya

Posted Tuesday, May 19, 2009 1:45 AM

Old Hand

Group: General Forum Members
Last Login: Monday, December 26, 2011 1:28 PM
Points: 346, Visits: 401

ok.

But I do not need row count i need count(name).

eg.
count(name) name surname
--------------------------------
1 may jar
2 may kar
3 may sar
4 apl ril
5 apl rol

then result should be
count(name) name surname
-----------------------------------
3 may jar
3 may kar
3 may sar
2 apl ril
2 apl rol

Post #719576

Peter Brinkhaus

Posted Tuesday, May 19, 2009 2:00 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:28 PM
Points: 1,561, Visits: 6,105

SELECT
  COUNT(*) OVER (PARTITION BY name), name, surname
FROM
  Info

Post #719585

Kruti Kansara

Posted Tuesday, May 19, 2009 2:00 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 09, 2011 2:49 AM
Points: 536, Visits: 412

select name, surname, (select count(*) from Info where name=a.name) as countname 
from Info a

"Don't limit your challenges, challenge your limits"

Post #719586

Samuel Vella

Posted Tuesday, May 19, 2009 2:07 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:22 AM
Points: 328, Visits: 1,852

mjarsaniya (5/19/2009)

This is why giving example input and output in the original question is a good idea!

Peter has the answer for you

Post #719589

mayank jarsaniya

Posted Tuesday, May 19, 2009 2:15 AM

Old Hand

Group: General Forum Members
Last Login: Monday, December 26, 2011 1:28 PM
Points: 346, Visits: 401

Thanks,
kruti, Peter Brinkhaus
your both's reply works and i get solution thanks............

Post #719593

« Prev Topic | Next Topic »

Permissions