按组SQL总计运行(Oracle) [英] Running Total by Group SQL (Oracle)
本文介绍了按组SQL总计运行(Oracle)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在Oracle数据库中有一个表,该表具有以下相关字段:位置,产品,日期,金额.我想编写一个查询,该查询将按位置,产品和日期获得运行总额.我在下面的示例表中列出了我希望得到的结果.
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 |
+----------+---------+-----------+------------+------------+
推荐答案
嗯,我想我已经解决了.
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
这篇关于按组SQL总计运行(Oracle)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文