Solved

get rolling total in sql statement

Posted on 2014-11-19
6
107 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

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

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 49

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach 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.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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