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, @AVG_COST, @check_total, @Check_amount, @amount_total
WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @RunningTotal_CHeck = @RunningTotal_CHeck + @checks
  set @RunningTotal_Amount = @RunningTotal_Amount + @Check_amount
  INSERT test VALUES (@tag, @Checks, @AVG_COST, @check_total, @Check_amount, @amount_total,  @RunningTotal_check, @RunningTotal_Amount )
  FETCH NEXT FROM aa_cursor INTO @tag, @Checks, @AVG_COST, @check_total, @Check_amount, @amount_total
 END

CLOSE aa_cursor
DEALLOCATE aa_cursor

SELECT *, RunningTotal_Check/Check_total as CHECK_RUN_PCT, round((RunningTotal_Check/Check_total *100),0) as CHECK_PCT_BIN,  RunningTotal_Amount/Amount_total as Amount_RUN_PCT,  round((RunningTotal_Amount/Amount_total * 100),0) as Amount_PCT_BIN
into test_4
FROM test ORDER BY tag
create clustered index IX_TESTsdsdds3 on test_4(tag)

DROP TABLE test

----------------------------------

I can the the running total for any 1 company but I would like to do it for multiple to produce something like the results below.

CLIENT  COUNT   Running Total
Company A   1   6.7%
Company A   2   20.0%
Company A   3   40.0%
Company A   4   66.7%
Company A   5   100.0%
Company B   1   3.6%
Company B   2   10.7%
Company B   3   21.4%
Company B   4   35.7%
Company B   5   53.6%
Company B   6   75.0%
Company B   7   100.0%
Company C   1   3.6%
Company C   2   10.7%
Company C   3   21.4%
Company C   4   35.7%
Company C   5   53.6%
Company C   6   75.0%
Company C   7   100.0%

Answer 1

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.