How to get cumulative sum (with past sum) in SQL Server
I am working with SQL Server 2008.
In my db, I have a table items
that has the following data:
id |date |title |price
-----------------------------------
1 |20150310 |A |100
2 |20150201 |B |25
3 |20140812 |C |225
4 |20130406 |D |110
5 |20150607 |E |645
6 |20120410 |F |450
7 |20130501 |G |325
8 |20150208 |H |175
I want to get the cumulative sum of the column price
after 20150101 (with the sum of the past).
I actually want the results to be the same:
date |title |price |cum_sum |before_cum_sum
---------------------------------------------------------------
|sum of past | | |1110
20150201 |B |25 |1135 |
20150208 |H |175 |1310 |
20150310 |A |100 |1410 |
20150607 |E |645 |2055 |
How do I get this kind of result in SQL Server?
+3
source to share
2 answers
The OP wants a certain way out - so I respect:
DECLARE @items TABLE ([date] DATETIME, title VARCHAR (100), price MONEY)
INSERT @items VALUES ('20150310','A',100), ('20150201','B',25 ), ('20140812','C',225), ('20130406','D',110), ('20150607','E',645), ('20120410','F',450), ('20130501','G',325), ('20150208','H',175)
;WITH p_cte AS (
SELECT
*
, RowNo = ROW_NUMBER() OVER (ORDER BY DATE)
FROM
@items
),
p2_cte AS (
SELECT
p_cte.date
, p_cte.title
, p_cte.price
, p_cte.RowNo
, cum_sum = price
, before_cum_sum = CONVERT (MONEY, 0)
FROM
p_cte
WHERE p_cte.RowNo = 1
UNION ALL
SELECT
cur.date
, cur.title
, cur.price
, cur.RowNo
, cum_sum = cur.price + prev.cum_sum
, before_cum_sum = prev.cum_sum
FROM
p_cte cur
INNER JOIN p2_cte prev ON
prev.RowNo = cur.RowNo - 1
)
SELECT TOP 1
date = CONVERT (DATETIME, NULL)
, title = 'sum of past'
, price = CONVERT (MONEY, NULL)
, rowno = CONVERT (INT, NULL)
, cum_sum = CONVERT (MONEY, NULL)
, p2_cte.before_cum_sum
FROM p2_cte WHERE date > '20150101'
UNION ALL
SELECT
*
FROM p2_cte WHERE date > '20150101'
The result looks like this:
+2
source to share