Solved

get rolling total in sql statement

Posted on 2014-11-19
6
103 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
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

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

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 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