Running Total by Group SQL (Oracle)

user1723699 picture user1723699 · Mar 3, 2014 · Viewed 39.1k times · Source

I have a table in an Oracle db that has the following fields of interest: Location, Product, Date, Amount. I would like to write a query that would get a running total of amount by Location, Product, and Date. I put an example table below of what I would like the results to be.

I can get a running total but I can't get it to reset when I reach a new Location/Product. This is the code I have thus far, any help would be much appreciated, I have a feeling this is a simple fix.

select a.*, sum(Amount) over (order by Location, Product, Date) as Running_Amt
from Example_Table a

+----------+---------+-----------+------------+------------+
| Location | Product | Date      | Amount     |Running_Amt |
+----------+---------+-----------+------------+------------+
| A        | aa      | 1/1/2013  | 100        | 100        |
| A        | aa      | 1/5/2013  | -50        | 50         |
| A        | aa      | 5/1/2013  | 100        | 150        |
| A        | aa      | 8/1/2013  | 100        | 250        |
| A        | bb      | 1/1/2013  | 500        | 500        |
| A        | bb      | 1/5/2013  | -100       | 400        |
| A        | bb      | 5/1/2013  | -100       | 300        |
| A        | bb      | 8/1/2013  | 250        | 550        |
| C        | aa      | 3/1/2013  | 550        | 550        |
| C        | aa      | 5/5/2013  | -50        | 600        |
| C        | dd      | 10/3/2013 | 999        | 999        |
| C        | dd      | 12/2/2013 | 1          | 1000       |
+----------+---------+-----------+------------+------------+

Answer

user1723699 picture user1723699 · Mar 3, 2014

Ah, I think I have figured it out.

select a.*, sum(Amount) over (partition by Location, Product order by Date) as Running_Amt
from Example_Table a