Calculat running totals column

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

Calculat running totals column

Author

Topic

ricc
Starting Member

16 Posts

Posted - 2006-07-13 : 05:58:26

Hi I have a table with the following structure:

UserID__DateJoined

I want to produce a query that shows on a month/year bases the number of users joined for each month AND show a running total of membership, I'm almost there....just got a problem getting the running totals:

this is what I have so far:

SELECT DATEPART(MONTH, DateJoined) AS Month, DATEPART(YEAR, DateJoined) AS Year, COUNT(UserID) AS NuAccounts
FROM Accounts_Users
GROUP BY DATEPART(MONTH, DateJoined), DATEPART(YEAR, DateJoined)
ORDER BY DATEPART(YEAR, DateJoined), DATEPART(MONTH, DateJoined)

How do I produce a column that shows the running total of accounts?

thanks for any help

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-07-13 : 06:00:40

Running Totals should be handled by the presentation layer.

Duane.

ricc
Starting Member

16 Posts

Posted - 2006-07-13 : 06:14:22

Thanks for the reply Duane, however I need it in this query as this will be used by other queries before the presentation layer.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-13 : 06:26:19

I think this will do

declare @output table (id int identity(0, 1), month tinyiny, year tinyint, NuAccounts INT)

INSERT		@output
SELECT		DATEPART(MONTH, DateJoined),
		DATEPART(YEAR, DateJoined),
		COUNT(UserID)
FROM		Accounts_Users
GROUP BY 	DATEPART(MONTH, DateJoined),
		DATEPART(YEAR, DateJoined)
ORDER BY 	DATEPART(YEAR, DateJoined),
		DATEPART(MONTH, DateJoined)


SELECT		o1.[month],
		o1.[year],
		o1.NuAccounts,
		(select sum(nuaccounts) from @output o2 where o2.id <= o1.id) RunningTotal
FROM		@output o1
order by	2,
		1

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-13 : 06:32:17

Here it is

declare @Accounts_Users table
(
	UserID		int identity(1,1),
	DateJoined	datetime
)

insert into @Accounts_Users(DateJoined)
select	'20060101'	union all
select	'20060102'	union all
select	'20060201'	union all
select	'20060201'	union all
select	'20060201'	union all
select	'20060301'	union all
select	'20060402'


select	DteJoined, NumAccounts, count(*) as TotalAccounts
from
(
	SELECT 	dateadd(month, datediff(month, 0, DateJoined), 0) as DteJoined, count(*) as NumAccounts
	FROM 	@Accounts_Users
	GROUP BY dateadd(month, datediff(month, 0, DateJoined), 0)
) m inner join @Accounts_Users u
on	m.DteJoined	>= dateadd(month, datediff(month, 0, u.DateJoined), 0)
group by DteJoined, NumAccounts
order by DteJoined

/* RESULT

DteJoined   NumAccounts TotalAccounts 
----------- ----------- ------------- 
2006-01-01  2           2
2006-02-01  3           5
2006-03-01  1           6
2006-04-01  1           7

*/

ricc
Starting Member

16 Posts

Posted - 2006-07-13 : 07:13:17

Thanks guys. Perfect.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-13 : 09:48:01

Also read this
http://sqlservercentral.com/cs/blogs/amachanic/archive/2006/02/28/508.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources