How can I create a cumulative report of data with SQL?

Question

I have this table DDL:

CREATE TABLE [dbo].[Audit] (
    [AuditId] INT              IDENTITY (1, 1) NOT NULL,
    [Entity]  INT              NOT NULL,
    [Action]  INT              NOT NULL,
    [Id]      UNIQUEIDENTIFIER NULL,
    CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED ([AuditId] ASC)
);

Update - I added the missing SQL here. Sorry about that

What I did was create a SQL report using this SQL that would show me how much activity there was each day:

select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
       [Col2] = convert(varchar, count(*))
from Audit a
group by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101)
order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc

The output looks like this:

col1        Col2
03/05/2017  1
03/04/2017  20
03/03/2017  10
03/02/2017  5
03/01/2017  10

What I now need to do is to create a SQL select that shows me a cumulative value and not the count. So I need SQL that clould create a report that would show this

col1        Col2
03/05/2017  46
03/04/2017  45
03/03/2017  25
03/02/2017  15
03/01/2017  10

Does anyone have any idea as to how I can modify my SQL to create this type of a report?

Note that I am really looking for a one command solution as it is executed from .net framework and if there are multiple commands in the solution I think I would need to work out how to put this into a stored procedure somehow.

the output is from a select * or?? How did you get 46 from a cumulative sum starting on the first row? — S3S, Mar 09 '17 at 15:44
This is a running total. The solution to this depends on the version of sql server you are using. There are dozens and dozens of examples of running totals on this site. — Sean Lange, Mar 09 '17 at 15:44
Hi @Anne, we would love to help you modify your SQL to get the right results. Unfortunately, you haven't provided your SQL code, so we don't know which modifications to suggest. — KindaTechy, Mar 09 '17 at 18:26
@EdwardRusu I am very sorry to have not included the SQL used to create the report. I will add it now. — , Mar 10 '17 at 06:41
Wondering why I have -2 votes for my question. Anyone have any ideas? — , Mar 10 '17 at 17:27

John Cappelletti · Accepted Answer · 2017-03-14T14:58:17.973

5

You can use the Window Functions Sum() Over

Select col1 = convert(varchar(10),cast(dateadd(HOUR, 8, a.date) as date),101)
      ,col2 = sum(count(*))  over (Order by cast(dateadd(HOUR, 8, a.date) as date))
 From  Audit a
 Group by cast(dateadd(HOUR, 8, a.date) as date)
 Order By cast(dateadd(HOUR, 8, a.date) as date) Desc

Returns

col1        Col2
03/05/2017  46
03/04/2017  45
03/03/2017  25
03/02/2017  15
03/01/2017  10

edited Mar 14 '17 at 14:58

answered Mar 09 '17 at 15:54

John Cappelletti

79,615
7
44
66

I am sorry I missed out mentioning that I already have a SQL select that I would like to modify to create the sum report. Your suggestion is good but is there a way that this could be tied into my original select that's now been added to the question. Thanks – Mar 10 '17 at 06:45
2

[Bad Habits to Kick : Using shorthand with date/time operations - Aaron Bertrand](https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations) – SqlZim Mar 13 '17 at 20:57
@JohnCappelletti I understand, you're used to the shorthand and it works well. I used it for a long time too. The argument against the shorthand is only for clarity because some of the less common abbreviations get pretty arcane and some may end up having to go and refer to the table of abbreviations just to figure out what something does. Granted, I think `hh` for `hour` would be an obvious one. I would upvote now, but I had already upvoted before I posted the previous comment. – SqlZim Mar 14 '17 at 15:03
@JohnCappelletti As a side note, even with the abbreviation it was the best answer here. – SqlZim Mar 14 '17 at 15:05
One more bad habit from [Itzik Ben-Gan](http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-part-1): "Stick to the `ROWS` form whenever possible and try to avoid indicating ordering without framing". You'd better write explicitly `SUM(...) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)` instead of leaving it out and defaulting to `RANGE`. In this specific case both variants will always produce the same results, but `ROWS` may be a bit more efficient. In general, the results may differ, so it is better to always explicitly say `ROWS` or `RANGE`. – Vladimir Baranov Mar 15 '17 at 02:57

score 4 · Answer 2 · answered Mar 14 '17 at 08:06

4

Use "with" statement to have the sum in one command. Like this:

WITH TBL ([col1], [col2])  
AS  
(  
SELECT [col1] = CONVERT(DATE, DATEADD(HOUR, 8, a.date)),
       [Col2] = COUNT(*)
FROM Audit a
GROUP BY CONVERT(DATE, DATEADD(HOUR, 8, a.date))
)  
SELECT  [col1] = CONVERT(VARCHAR(10), t1.[col1], 101), 
        [col2] = CONVERT(VARCHAR, SUM(t2.[Col2]))
FROM TBL t1 INNER JOIN TBL t2 on t1.col1 >= t2.col1
GROUP BY t1.col1
ORDER BY t1.col1 DESC

Good luck!

answered Mar 14 '17 at 08:06

VHao

732
7
7

This is your answer to get the cumulative value. Two things to keep in mind. 1- precede the WITH statement with a ;. 2- SQL server has a default max recursions (100 I believe); you can expand this with the MAXRECURSION() statement. – Jim Mar 18 '17 at 10:34

score 2 · Answer 3 · answered Mar 15 '17 at 08:03

if you give data sample of Audit Table and not data of your result set thn may be you get optmize and accurate query.

Using Sql 2012,

DECLARE @Audit TABLE (
    [col1]  datetime  NOT NULL
    ,[col2]  INT  NOT NULL
)

insert into @audit(col1,Col2)
VALUES
('03/05/2017',  1  )
,('03/04/2017',  20 )
,('03/03/2017',  10 )
,('03/02/2017',  5  )
,('03/01/2017',  10 )

;WITH CTE as
(
select col1 ,sum(col2) over(ORDER BY col1 desc  RANGE BETWEEN 
CURRENT ROW AND UNBOUNDED FOLLOWING) AS col2
from @Audit
)
SELECT * FROM cte   
order by col1 desc

@Anne,you can use Rows instead of Range depednding upon your real data. — KumarHarsh, Mar 15 '17 at 08:11

score 1 · Answer 4 · edited May 23 '17 at 12:32

1

You can try something like below.

IF OBJECT_ID('tempdb..#CumulativeSum') IS NOT NULL
    DROP TABLE #CumulativeSum

SELECT t1.col1, t1.col2 AS col2Actual, SUM(t2.col2) AS col2
INTO #CumulativeSum
FROM Audit t1
INNER JOIN Audit t2 on t1.col1 >= t2.col1
GROUP BY t1.col1, t1.col2
ORDER BY t1.col1

SELECT col1, col2 FROM #CumulativeSum ORDER BY col2 DESC

The answers was copied from below link as FiReTiTi suggested.

how to get cumulative sum

Edit - For having query into single command.

SELECT col1, col2 FROM(
    SELECT TOP 100 PERCENT t1.col1, t1.col2 AS col2Actual, SUM(t2.col2) AS col2
    FROM Audit t1
    INNER JOIN Audit t2 on t1.col1 >= t2.col1
    GROUP BY t1.col1, t1.col2
    ORDER BY t1.col1)x
ORDER BY col2 DESC

Hope this will help achieving your exact requirements.

edited May 23 '17 at 12:32

Community

1
1

answered Mar 09 '17 at 15:47

Naim Halai

355
1
8
27

Is there a way that I could get this into just the one command. The reason is I execute it from .net using exec command. With your implementation I would need to somehow put this into a stored procedure. Thanks – Mar 12 '17 at 13:23
create a stored script procedure... if you want to call it as one line. – RoMEoMusTDiE Mar 13 '17 at 22:03
Answer has been edited as Anne required the query to be in only a single statement. – Naim Halai Mar 14 '17 at 06:46

nazark · Answer 5 · 2017-03-13T12:32:22.660

1

try the below query

select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
       [Col2] = (select count(*) from Audit b where b.date<= DATEADD(HOUR, 8, a.date))
from Audit a
group by DATEADD(HOUR, 8, a.date)
order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc

edited Mar 13 '17 at 12:32

answered Mar 12 '17 at 11:24

nazark

1,240
2
10
15

score 1 · Answer 6 · answered Mar 18 '17 at 10:24

Only change your query by below query (this query works only when you are using SQL SERVER 2012)

Try This:

    DECLARE Audit TABLE (date datetime,
        [col1]  int  NOT NULL
    )

    insert into Audit(date,Col1)
    VALUES
    ('03/05/2017',  1  )
    ,('03/04/2017',  20 )
    ,('03/03/2017',  10 )
    ,('03/02/2017',  5  )
    ,('03/01/2017',  10 )


    select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
           [Col2] = SUM(col1)  over (Order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101))
    from Audit a
    order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc

How can I create a cumulative report of data with SQL?

6 Answers6