select statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » select statement

select statement

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Thursday, November 19, 2009 11:07 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Hi, I have a table named test. The table have this records:

Name: Numbers:
pedro 10
pedro 11
paulo 5
ana 7
ana 8
ana 9

What i want is to make a select that will display a count for each equal name:

Pedro 10 2
pedro 11 2
paulo 5 1
Ana 7 3
ana 8 3
ana 9 3

It will count the number of times that a name is equal.

how can i make the select?

Thank you.

Post #821823

GSquared

Posted Thursday, November 19, 2009 11:09 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

Inline correlated subquery is one way.

Without knowing your table structure, I can't really provide sample code for how to do it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #821826

Steve Jones - SSC Editor

Posted Thursday, November 19, 2009 11:10 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:46 PM
Points: 31,433, Visits: 13,745

This somewhat looks like homework, so I'll give you some hints here.

I'd build an aggregate query that uses COUNT() to determine how many times each name appears. That it a very simple aggregate. Then make that a subquery and join it back to the original table on names, and join in the count from that subquery to get that value.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #821831

river1

Posted Thursday, November 19, 2009 11:11 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

table structure is:

Name, numbers

Only this.

Post #821832

SQLRNNR

Posted Thursday, November 19, 2009 4:52 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

slightly different than the correlated subquery is the option of using a CTE. Use the cte to join back to the original table to get the results you are seeking.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #822021

« Prev Topic | Next Topic »

Permissions