SQL: Running total by record groups

Question

I'm trying to create a running total based on groups of records. My data looks like the below

Country | Genre | Month | Amt
A         X        1       5
A         X        2       3
B         X        1       8 
B         Y        1      10
A         X        1      12

I'd like to do a running sum on Amt grouped by unique occurences of Genre and Month for each Country. So my desired output is

Country | Genre | Month | Amt | RunAmt
A        X        1       5           5
A        X        2       3           3
B        X        1       8           8
B        Y        1      10         10
A        X        1      12         17

Since Genre = X and Month = 1 has occurred previously for country A.

My attempt has been sum(amt) over (partition by country, genre, month order by month rows between unbounded preceding and current row) but it seems to do a running sum over every row not for unique occurences by country. Any help would be appreciated.

Your attempt already returns the expected result (and both existing answers do exactly the same, if you add `rows...`). If this is not what you want you should include more data and an exact explanation for the correct result. — dnoeth, Sep 21 '15 at 06:09
Possible duplicate of [Calculate a Running Total in SQL Server](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — Black, Dec 31 '15 at 11:30

score 3 · Answer 1 · answered Sep 21 '15 at 04:45

Try this

SELECT *,
       Sum(amt)
         OVER(
           partition BY country, genre, month
           ORDER BY amt) as RunAmt
FROM  ( VALUES ('A','X',1,5),
               ('A','X',2,3),
               ('B','X',1,8),
               ('B','Y',1,10),
               ('A','X',1,12) )tc(Country, Genre, Month, Amt)

score 1 · Answer 2 · answered Sep 21 '15 at 04:55

1

Try this..

select *, 
    sum(amt) over 
    ( 
        partition by country, Genre, month order by amt
    ) as RunAmt 
from TableName

Here is the SQLFiddle for you that I tried.

answered Sep 21 '15 at 04:55

Amnesh Goel

2,617
3
28
47

SQL: Running total by record groups

2 Answers2