T-SQL: Compute Subtotals For A Range Of Rows
- by John Dibling
MSSQL 2008. I am trying to construct a SQL statement which returns the total of column B for all rows where column A is between 2 known ranges. The range is a sliding window, and should be recomputed as it might be using a loop.
Here is an example of what I'm trying to do, much simplified from my actual problem. Suppose I have this data:
table: Test
Year Sales
----------- -----------
2000 200
2001 200
2002 200
2003 200
2004 200
2005 200
2006 200
2007 200
2008 200
2009 200
2010 200
2011 200
2012 200
2013 200
2014 200
2015 200
2016 200
2017 200
2018 200
2019 200
I want to construct a query which returns 1 row for every decade in the above table, like this:
Desired Results:
DecadeEnd TotalSales
--------- ----------
2009 2000
2010 2000
Where the first row is all the sales for the years 2000-2009, the second for years 2010-2019. The DecadeEnd is a sliding window that moves forward by a set ammount for each row in the result set. To illustrate, here is one way I can accomplish this using a loop:
declare @startYear int
set @startYear = (select top(1) [Year] from Test order by [Year] asc)
declare @endYear int
set @endYear = (select top(1) [Year] from Test order by [Year] desc)
select @startYear, @endYear
create table DecadeSummary (DecadeEnd int, TtlSales int)
declare @i int
-- first decade ends 9 years after the first data point
set @i = (@startYear + 9)
while @i <= @endYear
begin
declare @ttlSalesThisDecade int
set @ttlSalesThisDecade = (select SUM(Sales) from Test where(Year <= @i and Year >= (@i-9)))
insert into DecadeSummary values(@i, @ttlSalesThisDecade)
set @i = (@i + 9)
end
select * from DecadeSummary
This returns the data I want:
DecadeEnd TtlSales
----------- -----------
2009 2000
2018 2000
But it is very inefficient. How can I construct such a query?
