Running Total For dataset grouped by month, year

Question

I am trying to create a running total using OVER ( PARTITION BY ) to create a running total.

My original query:

SELECT DATEPART(MONTH, t.received_date) AS [Month],
DATEPART(YEAR, t.received_date) AS [Year],
SUM(rdai.number_of_pages) AS [Count]
FROM dbo.request_document_additonal_information AS [rdai]
INNER JOIN #TempRequestIDs AS [t]
    ON rdai.request_id = t.id
GROUP BY DATEPART(MONTH, t.received_date),
DATEPART(YEAR, t.received_date)
ORDER BY Year,
Month;

And the results:

Month  Year  Count
10     2015  1202342
11     2015  1059471
12     2015  1142629
1      2016  1081412
2      2016  1181385
3      2016  1334966

My goal is to create a running sub-total for each month and my attempt to do so:

SELECT DATEPART(MONTH, t.received_date) AS [Month],
DATEPART(YEAR, t.received_date) AS [Year],
SUM(rdai.number_of_pages) AS [Count]
,SUM(rdai.number_of_pages) OVER (PARTITION BY DATEPART(MONTH, t.received_date), DATEPART(YEAR, t.received_date)
                                ORDER BY DATEPART(MONTH, t.received_date), DATEPART(YEAR, t.received_date)
                                RANGE UNBOUNDED PRECEDING
                               ) as [RunningTotal]
FROM dbo.request_document_additonal_information AS [rdai]
INNER JOIN #TempRequestIDs AS [t]
    ON rdai.request_id = t.id
GROUP BY DATEPART(MONTH, t.received_date),
DATEPART(YEAR, t.received_date)
ORDER BY Year,
Month;

But the error returned states:

Column 'dbo.request_document_additonal_information.number_of_pages' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And if I add GROUP BY .... rdai.number_of_pages, the running total is listed but it is the same number for all the columns.

Could I get some assistance on where I am going wrong using this Windowed function?

Thanks,

Answer 1

One option is to nest your original query

Select A.*
      ,RunningTotal = sum(count) over (Order by Year ,Month)
 From (
        SELECT DATEPART(MONTH, t.received_date) AS [Month],
        DATEPART(YEAR, t.received_date) AS [Year],
        SUM(rdai.number_of_pages) AS [Count]
        FROM dbo.request_document_additonal_information AS [rdai]
        INNER JOIN #TempRequestIDs AS [t]
            ON rdai.request_id = t.id
        GROUP BY DATEPART(MONTH, t.received_date),
        DATEPART(YEAR, t.received_date)
      ) A
ORDER BY Year,Month;