Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select statement Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 11:07 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:58 PM
Points: 753, Visits: 1,070
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
Posted Thursday, November 19, 2009 11:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Thursday, November 19, 2009 11:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:11 AM
Points: 31,168, Visits: 15,611
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
Posted Thursday, November 19, 2009 11:11 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:58 PM
Points: 753, Visits: 1,070
table structure is:


Name, numbers

Only this.
Post #821832
Posted Thursday, November 19, 2009 4:52 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 17,807, Visits: 15,724
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #822021
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse