12

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       |
+----------+---------+-----------+------------+------------+
user1723699
  • 1,031
  • 6
  • 13
  • 27

3 Answers3

14

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
user1723699
  • 1,031
  • 6
  • 13
  • 27
7

from Advanced SQL Functions in Oracle 10g book, it has this example.

SELECT dte "Date", location, receipts,
SUM(receipts) OVER(ORDER BY dte
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) "Running total"
FROM store
WHERE dte < '10-Jan-2006'
ORDER BY dte, location
chip
  • 3,039
  • 5
  • 35
  • 59
-3

I could type out all the answer or send you to where I learned it. :)

Check this out, it explains exactly what you are trying to do.

http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server

Joe Trader
  • 17
  • 2
  • You don't find the article that explains how to partition the data useful? Although the answer is partitioning. – Joe Trader Mar 03 '14 at 19:39
  • I didn't downvote this answer, but I think there is definitely room for improvement. Maybe consider adding more context/content - there seems to be a consensus on SO that [links alone are not sufficient for a good answer](http://meta.stackexchange.com/questions/7515/why-is-linking-bad). – crennie Mar 03 '14 at 19:49
  • As an aside, this link may have been more appropriate as a comment instead of an answer... but unfortunately you need 50 rep for that. If you're like me and you thought that was lame, then check out [this question about it on meta](http://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead), which gives the limitation some motivation, and also suggests some ways to write a great answer :D – crennie Mar 03 '14 at 19:54
  • The linked article is for SQL Server. Oracle has a much more efficient way of doing this using a window function. –  Mar 03 '14 at 21:55
  • Thanks for taking the time to criticize my answer which solved the problem. Many thanks for mentioning that oracle has a more efficient way but not bothering to answer the question. It would have been more helpful if you actually provided some instruction on your efficient way. The answer I referenced works in oracle the same way it does in SQL Server. – Joe Trader Apr 11 '15 at 03:56