Record Count by Group - 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 2000 Forums

SQL Server Development (2000)

Record Count by Group

Author

Topic

Togaspoon
Starting Member

42 Posts

Posted - 2002-02-07 : 17:09:20

Is there a way to return a record count that restarts with a group change?

Expamle
1 NFL
2 NFL
3 NFL
1 MLB
2 MLB

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-02-07 : 17:25:09

quote:

Is there a way to return a record count that restarts with a group change?

Expamle
1 NFL
2 NFL
3 NFL
1 MLB
2 MLB

select count(field), field from table GROUP by field

Cat

Togaspoon
Starting Member

42 Posts

Posted - 2002-02-07 : 17:44:55

Sorry, I guess my question wasn't that clear.

I need my recordset to look like the example above, I'm not sure how to create the first column. These values are not in any table and would simple be a number that increments by 1 for each record in the group.

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-02-07 : 20:53:23

Try something like the following.

---
create table #Group (
GroupId int identity(1,1)
, GroupName varchar(50)
)
go

insert into #group (GroupName) values ('nfl')
insert into #group (GroupName) values ('nfl')
insert into #group (GroupName) values ('nfl')
insert into #group (GroupName) values ('mlb')
insert into #group (GroupName) values ('mlb')
go

select
Grp.GroupName
, (select count(*) GroupName from #Group where GroupName=Grp.GroupName and GroupId <= Grp.GroupId) as GroupIncrement
from
#Group as Grp
order by
Grp.GroupName, Grp.GroupId

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-08 : 01:15:40

quote:

Try something like the following.

Now I could be guessing - but I suspect you wanted something a little more generic???

here it is
create table #result (id int, col1 nvarchar(50))

declare @sCol1 nvarchar(50)

declare incrementor cursor
for select distinct col1 from c

open incrementor
fetch next from incrementor
into @sCol1

while @@FETCH_STATUS = 0
begin
create table #a (id int IDENTITY(1,1), col1 nvarchar(50) )
insert into #a (col1) select col1 from c where col1 = @sCol1

insert into #result (id, col1)
select id, col1 from #a
drop table #a

fetch next from incrementor
into @sCol1
end

close incrementor
deallocate incrementor

select id, col1 from #result order by col1, id
drop table #result

now i'm sure all the Yak masters can show you how to do it without a cursor - but I'm not that clever

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

sica
Posting Yak Master

143 Posts

Posted - 2002-02-08 : 06:38:37

Maybe this will help you:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12394

Sica

Togaspoon
Starting Member

42 Posts

Posted - 2002-02-08 : 11:15:26

That's what I needed, thanks for all the quick responses.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-09 : 07:20:04

quote:

That's what I needed, thanks for all the quick responses.

which was what you needed? Can anyone do it without a cursor? Where's nr when you need him.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources