SUM OVER PARTITION to calculate running total

Question

I am trying to modify my query to include a running total for each county in my report. Below is my working query with an attempt to use SUM OVER PARTITION commented out:

SELECT  DATEPART(MONTH, r.received_date) AS [MonthID] ,
    DATENAME(MONTH, r.received_date) AS [Month] ,
    o.name AS [CountyName] ,
    rsc.description AS [Filing] ,
    COUNT(r.id) AS [Request_Total] ,
    CAST (AVG(CAST (DATEDIFF(HOUR, received_date, completion_date) AS DECIMAL(8,2))) / 24 AS DECIMAL(8,2)) AS [Total_Time_Days] 
    --SUM(r.id) OVER (PARTITION BY o.name) AS [TotalFilings]
FROM    dbo.requests AS [r]
    INNER JOIN dbo.organizations AS [o] ON o.id = r.submitted_to_organiztion_id
    INNER JOIN dbo.request_status_codes AS [rsc] ON rsc.code = r.request_status_code
WHERE   r.submitted_to_organiztion_id < 68
    AND r.request_type_code = 1
    AND CAST(r.received_date AS DATE) >= '01/01/2016'
    AND CAST(r.received_date AS DATE) <= '06/30/2016'
    AND o.name = 'Alachua'
GROUP BY DATENAME(MONTH, r.received_date) ,
    DATEPART(MONTH, r.received_date) ,
    o.name ,
    rsc.description
ORDER BY DATEPART(MONTH, r.received_date) ,
    CountyName ,
    Filing;

And the results look correct:

Perhaps I am misusing the SUM PARTITION BYbut my end goal is to add an additional column that will sum the filing types for each county by month.

For example, the additional column for the month of January should be 13,654 while February should be 14,238 and so on.

Could I get some advice on how to get this query working correctly? Thanks,

Possible duplicate of [Calculate a Running Total in SQL Server](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — dfundako, Jul 11 '16 at 17:25
So for records 1 through 4, you would expect to see the values of the `Total Filings` columns to be 70/13654/13698/27892 or would you expect to see 70/13654/44/14238? — tarheel, Jul 11 '16 at 17:26
@MISNole Is 70/13654/13698/27892 or 70/13654/44/14238 correct? — tarheel, Jul 11 '16 at 20:06
I'm sorry - I misread your original comment. The later would be correct - I would like the totals to reset each month - and then obviously reset on change of county as well. My final query would see the o.name = 'Alachua' removed and this would provide a running total across all 67 counties in Florida. — MISNole, Jul 11 '16 at 20:13
@MISNole I believe the only thing you need to do is take advantage of the `order by` clause inside of the `over` clause. LIke this: `SUM(r.id) OVER (PARTITION BY o.name order by rsc.description) AS [TotalFilings]` — tarheel, Jul 12 '16 at 03:51

score 0 · Answer 1 · answered Jul 11 '16 at 20:33

Not sure this is the best way or more efficient, but I was able to create a sub-query to obtain the results I wanted. I do believe a CTE or use of a Windows function would be better, but I haven't been able to get it to work. Here is my query however:

SELECT  X.[MonthID] ,
    X.[Month] ,
    X.[CountyName] ,
    X.[Filing] ,
    X.[Avg_Time_Days] ,
    SUM(X.Request_Total) AS [Total_Requests]
FROM    ( SELECT    DATEPART(MONTH, r.received_date) AS [MonthID] ,
                DATENAME(MONTH, r.received_date) AS [Month] ,
                o.name AS [CountyName] ,
                rsc.description AS [Filing] ,
                COUNT(r.id) AS [Request_Total] ,
                CAST (AVG(CAST (DATEDIFF(HOUR, received_date,
                                         completion_date) AS DECIMAL(8, 2)))
                / 24 AS DECIMAL(8, 2)) AS [Avg_Time_Days]
    --, SUM(r.id) OVER (PARTITION BY o.name, rsc.description) AS [TotalFilings]
      FROM      dbo.requests AS [r]
                INNER JOIN dbo.organizations AS [o] ON o.id = r.submitted_to_organiztion_id
                INNER JOIN dbo.request_status_codes AS [rsc] ON rsc.code = r.request_status_code
      WHERE     r.submitted_to_organiztion_id < 68
                AND r.request_type_code = 1
                AND CAST(r.received_date AS DATE) >= '01/01/2016'
                AND CAST(r.received_date AS DATE) <= '06/30/2016'
    --AND o.name = 'Alachua'
GROUP BY            DATENAME(MONTH, r.received_date) ,
                DATEPART(MONTH, r.received_date) ,
                o.name ,
                rsc.description
    --, r.id
--ORDER BY DATEPART(MONTH, r.received_date) ,
--        CountyName ,
--        Filing
    ) AS X
GROUP BY X.[MonthID] ,
    X.[Month] ,
    X.[CountyName] ,
    X.[Filing] ,
    X.[Avg_Time_Days]
ORDER BY X.[MonthID] ,
    X.[Month] ,
    X.[CountyName] ,
    X.[Filing];

SUM OVER PARTITION to calculate running total

1 Answers1