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.
| 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 dodeclare @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
*/
KH
|
|
|
|
ricc
Starting Member
16 Posts |
Posted - 2006-07-13 : 07:13:17
|
 Thanks guys. Perfect. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Calculat running totals column