Sum of previous n number of columns based on some category
On SQL Server 2014 and 2016 1 you can use a WINDOW
function (i.e. an OVER
clause) to perform what you want:
SELECT
category, year, week, value,
sum(value) OVER (PARTITION BY category
ORDER BY year, week
ROWS 2 PRECEDING) AS retention_value_3_weeks
FROM
t
ORDER BY
category, year, week ;
And this is the result you'll get:
category | year | week | value | retention_value_3_weeks :------- | ---: | ---: | ----: | ----------------------: a | 2016 | 1 | 5 | 5 a | 2016 | 2 | 7 | 12 a | 2016 | 3 | 8 | 20 b | 2016 | 3 | 6 | 6 b | 2016 | 4 | 15 | 21 b | 2016 | 5 | 25 | 46 c | 2016 | 3 | 25 | 25 c | 2016 | 4 | 2 | 27 c | 2016 | 5 | 21 | 48 c | 2016 | 6 | 26 | 49
NOTE that the x = 3
of your example gets translated to (the current row and the 2 preceding
ones).
If, for some reason, you can't use the OVER
clause, you can still compute the same results using some (quite convoluted) subqueries:
SELECT
category, year, week, value,
(SELECT
sum(value)
FROM
(SELECT TOP 3 /* total number of rows to consider */
value
FROM
t t2
WHERE
t2.category = t.category /* partition by category */
AND t2.week <= t.week /* current and preceding rows */
ORDER BY
year DESC, week DESC /* order by criteria */
) AS q
) AS retention_value_3_weeks
FROM
t
ORDER BY
category, year, week ;
Check it all at dbfiddle here
If you want to use @x
instead of 3
, you can do so:
DECLARE @x AS INTEGER = 3;
SELECT
category, year, week, value,
(SELECT
sum(value)
FROM
(SELECT TOP (@x) /* total number of rows to consider */
value
FROM
t t2
WHERE
t2.category = t.category /* partition by category */
AND t2.week <= t.week /* current and preceding rows */
ORDER BY
year DESC, week DESC /* order by criteria */
) AS q
) AS retention_value_3_weeks
FROM
t
ORDER BY
category, year, week ;;
dbfiddle here
1) Can't test with SQL Server 2012 because I don't have one. The documentation from MS SQL Server indicate it should be available since version 2008.