Tip for Using SQLServer To Count By Alpha Names in a list

 

So, this is not a big tip, but worth at least 10 minutes to figure it out on your own.  Here is to saving you 10 minutes:

 

SELECT LEFT (userlastname, 1) as alpha,
count(id)
FROM attendees
WHERE id IN (
SELECT attendeesid
FROM AttendeesCodeCampYear
WHERE codecampyearid = 6
)
GROUP BY LEFT (userlastname, 1)O
ORDER BY LEFT (userlastname, 1)

It’s pretty self explanatory.  Our case is we have two tables that we track code camp attendees.  One is the master list, and the other is a detail by year.  (6 is this year)

 

image

And, the results:

> NULL    1

>        6

> .       1

> @       1

> _       18

> A       319

> B       488

> C       543

> Ç       2

> D       313

> E       84

> F       169

> g       368

> H       340

> I       49

> J       190

> K       474

> L       460

> M       592

> N       255

> O       79

> P       427

> Q       14

> R       336

> S       776

> T       302

> u       27

> V       191

> w       296

> X       21

> y       176

> Z       100

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Comments

  1. Trina Desmaris says:

    As a satisfied customer of Acceloweb I recommend this product highly. Thanks to Acceoweb and Rackspace for a job well done

  2. Gaylene Yousif says:

    Other online backup services that offer seeded backup include CrashPlan and Mozy (Data Shuttle).

Follow

Get every new post delivered to your Inbox

Join other followers: