Solved

get rolling total in sql statement

Posted on 2014-11-19
6
109 Views
Last Modified: 2014-11-19
Hi, I have a query that select the current financial year and a count of users authenticated in that year, I would also like a 'rolling total', can someone explain how I would do this please?  Many thanks in advance.

	(SELECT count( D1.UserID) AS Users, FinYear
FROM ext_UserAudit AS D1
INNER JOIN Dates
ON CONVERT(VARCHAR,  D1.AuditDate, 110) = CONVERT(VARCHAR, Dates.[DATE], 110)
WHERE  D1.AuditType = 'User Authenticated'
GROUP BY FinYear)

Open in new window


Gives the results:

Users      FinYear
3564      2011
23455      2012
25634      2013
27449      2014

I would like the results:

Users      FinYear    RollingTotal
3564      2011         3564
23455      2012         27019
25634      2013         52653
27449      2014         80102
0
Comment
Question by:deborahhowson00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40452071
add at the end of line 1
, count( D1.UserID) OVER(ORDER BY D1.FinYear) AS RollingTotal

Open in new window

0
 

Author Comment

by:deborahhowson00
ID: 40452111
Thanks for your comment, doing that gives me the following code (had to remove the D1. before FinYear):

	(SELECT count( D1.UserID) AS Users, FinYear, count( D1.UserID) OVER(ORDER BY FinYear) AS RollingTotal
FROM ext_UserAudit AS D1
INNER JOIN Dates
ON CONVERT(VARCHAR,  D1.AuditDate, 110) = CONVERT(VARCHAR, Dates.[DATE], 110)
WHERE  D1.AuditType = 'User Authenticated'
GROUP BY FinYear)

Open in new window


but it runs with the following error:

Msg 8120, Level 16, State 1, Line 1
Column 'ext_UserAudit.UserID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40452123
Let's try this instead:

With myTable as 
	(SELECT count( D1.UserID) AS Users, FinYear
FROM ext_UserAudit AS D1
INNER JOIN Dates
ON CONVERT(VARCHAR,  D1.AuditDate, 110) = CONVERT(VARCHAR, Dates.[DATE], 110)
WHERE  D1.AuditType = 'User Authenticated'
GROUP BY FinYear)

Select Users, FinYear, Sum(Users) OVER(ORDER BY FinYear) AS RollingTotal
from myTable

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:deborahhowson00
ID: 40452128
That's brilliant, thank you! :D
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40452145
I don't think that the ROLLUP it's the solution that you are looking for,
You can do a simple test that is to add the ROLLUP keyword at the end of your query to see the results by yourself:
(SELECT count( D1.UserID) AS Users, FinYear
FROM ext_UserAudit AS D1
INNER JOIN Dates
ON CONVERT(VARCHAR,  D1.AuditDate, 110) = CONVERT(VARCHAR, Dates.[DATE], 110)
WHERE  D1.AuditType = 'User Authenticated'
GROUP BY FinYear WITH ROLLUP)

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40452148
Ok, just now I saw that Phillip also realized that :)
Anyway, you can still test the ROLLUP just to check the difference on the results.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question