Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# get rolling total in sql statement

Posted on 2014-11-19
Medium Priority
115 Views
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)
``````

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
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
• 2
• 2
• 2

LVL 24

Expert Comment

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

Author Comment

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)
``````

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

Phillip Burton earned 2000 total points
ID: 40452123

``````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
``````
0

Author Comment

ID: 40452128
That's brilliant, thank you! :D
0

LVL 52

Expert Comment

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)
``````
0

LVL 52

Expert Comment

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

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
###### Suggested Courses
Course of the Month9 days, 14 hours left to enroll