Cumulative Count - 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

General SQL Server Forums

New to SQL Server Programming

Cumulative Count

Author

Topic

lakshmi
Starting Member

2 Posts

Posted - 2005-10-04 : 16:25:14

I have to group the no. of employees based on month. i.e

Jan 10 employees
Feb 20 employees
Mar 30 employess

The result is to be

Jan 10
Feb 20
Mar 60.

Kindly provide the sql for getting the above result.

Thanks,
Lakshmi

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-04 : 16:29:41

>>>Jan 10 employees
>>>Feb 20 employees
>>>Mar 30 employess

>>>The result is to be

>>>Jan 10
>>>Feb 20
>>>Mar 60.

Why should March be 60 when Jan and feb are exactly the same as the input data?

should the result by any chance be
Jan 10
Feb 30
Mar 60.

Duane.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-04 : 16:39:15

Running totals article:
http://www.sqlteam.com/item.asp?ItemID=3856

Tara

lakshmi
Starting Member

2 Posts

Posted - 2005-10-04 : 17:34:03

quote:
Originally posted by ditch

>>>Jan 10 employees
>>>Feb 20 employees
>>>Mar 30 employess

>>>The result is to be

>>>Jan 10
>>>Feb 20
>>>Mar 60.

Why should March be 60 when Jan and feb are exactly the same as the input data?

should the result by any chance be
Jan 10
Feb 30
Mar 60.

Duane.

Yeah, u r right duane. feb should be 30. I had it wrong.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-04 : 17:36:11

OK then check out that article that Tara referenced - that should do it :)

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 01:42:05

Lakshmi, where do you want to show these records?

If you want to show them in Reports, then use its running total feature
If you do it in a query, there will be performance issues if your table has thousands of records

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-05 : 03:41:19

I agree with Madhivanan, but just for fun here is the solution that is not mentioned in the article:

declare @result table ([month] char(3) primary key, newEmployees int, total int)

insert into @result
select [month], newEmployees, 0
from newEmployeesPerMonth

declare @total int
set @total = 0

update r
  set @total = r.total = @total + s.newEmployees
  from  newEmployeesPerMonth s (index=idx_month)
  inner loop join @reusult r on r.[month]= s.[month]
  option (force order)

select *
from @result
order by [month]

It works if idx_month is clustered index on [month] column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources