So, I have the following query,
WITH yearlist AS
(
SELECT (year(getdate())+3) AS years
UNION ALL
SELECT y.years - 1 AS years
FROM yearlist y
WHERE y.years - 1 >= (YEAR(GETDATE())-10)
)
SELECT
a.years as [year],
a.CountryName as country,
ISNULL(sum(b.sales), 0) as total
FROM(
SELECT
distinct years
,g.CountryName
FROM
yearlist AS A CROSS JOIN (SELECT
CountryName, salesYear, ISNULL(sum(sales), 0) as total
FROM tblSales where
salesYear BETWEEN (year(getdate())-12) AND (year(getdate()) + 3)
,sales
,salesYear) g
) a left outer join
(SELECT
CountryName, salesYear, ISNULL(sum(sales), 0) as total
FROM tblSales where
salesYear BETWEEN (year(getdate())-12) AND (year(getdate()) + 3)
group by CountryName
,salesYear, sales
) b ON a.CountryName=b.CountryName and a.years=b.salesYear
group by a.CountryName,years
order by years
I am getting the following returned:
year country Total
---------- ---------------------------------------- -------
2009 France 0.00
2009 Japan 0.00
2009 Norway 2.30
2009 Portugal 0.00
2009 South Korea 0.00
2009 Spain 0.00
2009 Sweden 0.00
2009 United Kingdom 0.00
2009 United States 0.00
2010 France 0.00
2010 Japan 0.00
2010 Norway 0.00
2010 Portugal 0.00
2010 South Korea 0.00
2010 Spain 0.00
2010 Sweden 0.00
2010 United Kingdom 0.00
2010 United States 0.00
2011 France 0.00
2011 Japan 0.00
2011 Norway 0.00
2011 Portugal 2.00
2011 South Korea 0.00
2011 Spain 0.00
2011 Sweden 0.00
2011 United Kingdom 0.00
2011 United States 0.00
2012 France 0.00
2012 Japan 0.01
2012 Norway 0.00
2012 Portugal 0.00
2012 South Korea 0.00
2012 Spain 0.00
2012 Sweden 0.00
2012 United Kingdom 0.00
2012 United States 0.00
2013 France 0.00
2013 Japan 2.00
2013 Norway 0.00
2013 Portugal 0.00
2013 South Korea 0.00
2013 Spain 0.00
2013 Sweden 0.00
2013 United Kingdom 0.00
2013 United States 0.00
I am trying to achieve a cumulative total for each country, as the years increase. But I cant seem to get it. I've tried this:
sum(sales) over (order by salesYear rows unbounded preceding) as total
But that just filled each row with the cumulative total.
The output I desire is as follows:
year country Total
---------- ---------------------------------------- -------
2009 France 0.00
2010 France 0.00
2011 France 0.00
2009 Japan 0.00
2010 Japan 0.00
2011 Japan 0.00
2009 Norway 2.30
2010 Norway 2.30
2011 Norway 2.30
2009 Portugal 0.00
2010 Portugal 0.00
2011 Portugal 2.00
2009 South Korea 0.00
2010 South Korea 0.00
2011 South Korea 0.00
2009 Spain 0.00
2010 Spain 0.00
2011 Spain 0.00
2009 Sweden 0.00
2010 Sweden 0.00
2011 Sweden 0.00
2009 United Kingdom 0.00
2010 United Kingdom 0.00
2011 United Kingdom 0.00
2009 United States 0.00
2010 United States 0.00
2011 United States 0.00
I just cant seem to get them to individually accumulate.
You most likely need a partition by clause, too:
sum(sum(sales)) over (partition by country order by salesYear rows unbounded preceding)