Solved

get rolling total in sql statement

Posted on 2014-11-19
6
94 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:deborahhowson00
Comment Utility
That's brilliant, thank you! :D
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now