I need to COUNT some people - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

I need to COUNT some people

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-04-19 : 20:08:27

Ok, I have a table full of classes and students within these classes like so:

Art 101
Sam
Dave
Mike
Steve
...

English 324
Robert
Lisa
Margret
Jen
......

Now I need to count these students like so for each class:
Art 101
1. Sam
2. Dave
3. Mike
4. Steve
...

English 324
1. Robert
2. Lisa
3. Margret
4. Jen
......

Any way to do this in SQL or am I screwed??
--Nick

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-19 : 21:28:00

you can do the numbering in the front end or use the row_number() function

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-19 : 22:45:26

Is this what you want?


create table T_ClassInfo(Class varchar(50),Student varchar(50))

insert into t_Classinfo(Class,Student)
select 'Art 101','Sam'
Union all
select 'Art 101','Dave'
Union all
select 'Art 101','Mike'
Union all
select 'Art 101','Steve'
Union all
select 'English 324','Robert'
Union all
select 'English 324','Lisa'
Union all
select 'English 324','Mark'
Union all
select 'English 324','Jen'
Union all
select 'English 324','Tommy'


Select a.class,Count(*)
from T_ClassInfo a
group by a.Class

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-04-20 : 14:58:38

Yeah, not quite. That query actually counts the number of people within the class. I needed it to put a number by the student name like so:

Art 101
1. Sam
2. Greg
3. Mary
...

Math 345
1. Susan
2. Tony
3. David
....

Grouped by class, but w/e I don't even know if this is possible.

--Nick

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-20 : 20:02:45

[code]
Select a.class,a.student,Row_Number() over(partition by a.class order by a.Student) as Rank
from T_ClassInfo a
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources