Multiple Running Totals with Group By

Question

I am struggling to find a good way to run running totals with a group by in it, or the equivalent. The below cursor based running total works on a complete table, but I would like to expand this to add a "Client" dimension. So I would get running totals as the below creates but for each company (ie Company A, Company B, Company C, etc.) in one table ``` CREATE TABLE test (tag int, Checks float, AVG_COST float, Check_total float, Check_amount float, Amount_total float, RunningTotal_Check float, RunningTotal_Amount float) DECLARE @tag int, @Checks float, @AVG_COST float, @check_total float, @Check_amount float, @amount_total float, @RunningTotal_Check float , @RunningTotal_Check_PCT float, @RunningTotal_Amount float SET @RunningTotal_Check = 0 SET @RunningTotal_Check_PCT = 0 SET @RunningTotal_Amount = 0 DECLARE aa_cursor CURSOR fast_forward FOR SELECT tag, Checks, AVG_COST, check_total, check_amount, amount_total FROM test_3 OPEN aa_cursor FETCH NEXT FROM aa_cursor INTO @tag, @Checks, @…

Accepted Answer

This is finally simple to do in SQL Server 2012, where SUM and COUNT support OVER clauses that contain ORDER BY. Using Cris's #Checks table definition:

```
SELECT
  CompanyID,
  count(*) over (
    partition by CompanyID
    order by Cleared, ID
  ) as cnt,
  str(100.0*sum(Amount) over (
    partition by CompanyID
    order by Cleared, ID
  )/
  sum(Amount) over (
    partition by CompanyID
  ),5,1)+'%' as RunningTotalForThisCompany
FROM #Checks;
```

SQL Fiddle here.

Multiple Running Totals with Group By

Solution

Problem