Running Totals

Hi,
I have question regarding running totals, here is my SQL code so far:
SELECT Month(DateUsed) as Months,
COUNT(DateUsed) as NumUsed
FROM TempTable
GROUP BY DateUsed
What I also want in there is another column giving me the running total of
'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
obviously this wont work. I have tried using ROLLUP but that does not give me
what I want.
Here is an example of the data I want back.
Months NumUsed RunningTotal
114 14
710 24
1044 68
Is there another way to do this?
Thanks in advance.
Kind regards,
J
Please post your DDL and sample data.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:5B340CE7-5536-402A-8E30-6B2C14EB759C@.microsoft.com...
Hi,
I have question regarding running totals, here is my SQL code so far:
SELECT Month(DateUsed) as Months,
COUNT(DateUsed) as NumUsed
FROM TempTable
GROUP BY DateUsed
What I also want in there is another column giving me the running total of
'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
obviously this wont work. I have tried using ROLLUP but that does not give
me
what I want.
Here is an example of the data I want back.
Months NumUsed RunningTotal
1 14 14
7 10 24
10 44 68
Is there another way to do this?
Thanks in advance.
Kind regards,
J
|||Something like :
SELECT Month(DateUsed) as Months,
COUNT(DateUsed) as NumUsed,
(SELECT COUNT(DateUsed)
FROM TempTable T2
WHERE T2.Month(DateUsed) <= T1.Month(DateUsed)) AS RunningTotal
FROM TempTable T1
GROUP BY DateUsed
A +
JenC a écrit :
> Hi,
> I have question regarding running totals, here is my SQL code so far:
> SELECT Month(DateUsed) as Months,
> COUNT(DateUsed) as NumUsed
> FROM TempTable
> GROUP BY DateUsed
> What I also want in there is another column giving me the running total of
> 'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
> obviously this wont work. I have tried using ROLLUP but that does not give me
> what I want.
> Here is an example of the data I want back.
> Months NumUsed RunningTotal
> 114 14
> 710 24
> 1044 68
>
> Is there another way to do this?
> Thanks in advance.
> Kind regards,
> J
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Hi Tom,
Just in the process of posting the required info when i read the post by
SQLPro which gets the results I want.
Thanks for your time. J
"Tom Moreau" wrote:

> Please post your DDL and sample data.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "JenC" <JenC@.discussions.microsoft.com> wrote in message
> news:5B340CE7-5536-402A-8E30-6B2C14EB759C@.microsoft.com...
> Hi,
> I have question regarding running totals, here is my SQL code so far:
> SELECT Month(DateUsed) as Months,
> COUNT(DateUsed) as NumUsed
> FROM TempTable
> GROUP BY DateUsed
> What I also want in there is another column giving me the running total of
> 'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
> obviously this wont work. I have tried using ROLLUP but that does not give
> me
> what I want.
> Here is an example of the data I want back.
> Months NumUsed RunningTotal
> 1 14 14
> 7 10 24
> 10 44 68
>
> Is there another way to do this?
> Thanks in advance.
> Kind regards,
> J
>
|||Hi,
Thanks for this, works a treat. J
"SQLpro [MVP]" wrote:

> Something like :
> SELECT Month(DateUsed) as Months,
> COUNT(DateUsed) as NumUsed,
> (SELECT COUNT(DateUsed)
> FROM TempTable T2
> WHERE T2.Month(DateUsed) <= T1.Month(DateUsed)) AS RunningTotal
> FROM TempTable T1
> GROUP BY DateUsed
> A +
> JenC a écrit :
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>