Need help with cumulative counts

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)

Need help with cumulative counts

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-07-08 : 11:42:55

Hello,

Let's say I have a table like #mytable below:

------------------------
create table #mytable
(
date_ smalldatetime null,
id varchar(3) null,
val_ float null
)
go

insert into #mytable
(date_, id, val_)
values
('2000-01-01', 'ABC', 4.5)

insert into #mytable
(date_, id, val_)
values
('2000-01-02', 'ABC', 4.2)

insert into #mytable
(date_, id, val_)
values
('2000-01-02', 'DEF', 8.9)

insert into #mytable
(date_, id, val_)
values
('2000-01-03', 'ABC', 4.6)

insert into #mytable
(date_, id, val_)
values
('2000-01-03', 'DEF', 9.9)

insert into #mytable
(date_, id, val_)
values
('2000-01-03', 'GHI', 22.0)
go

select * from #mytable order by date_, id
go

drop table #mytable
go
----------------------------
while the results of this give me something like:

date_--id--val_

I would like to add another column 'count' as:

date_--id--val_--count

which would be a cumulative count of the number of items the given id has appeared in the table on or before the date of record. Thus, something like:

select count(*) from #mytable where id = 'ABC' and date_ = '2000-01-03' would give me 3, 2 for the day before that, and 1 for the day before that. Does anyone have any suggestions as to how I could incorporate such a 'cumulative count' query?

Any help is much appreciated.

rohar
Starting Member

4 Posts

Posted - 2005-07-08 : 12:54:04

Something like this?:

SELECT
* ,
(
SELECT
count(*)
FROM #mytable m
WHERE id = #mytable.id
AND date_ < #mytable.date_
) AS prevcount
FROM #mytable
ORDER BY date_,
id

ROh

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-07-08 : 13:35:10

I think so...a little change and that should do it.

I appreciate your help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources