Using GROUP BY and OVER

Question

I'm trying to get the total sales grouped by year and country. This works as expected:

SELECT Year, Country, SUM([Total Sales])
FROM Table
GROUP BY Country, Year;

Then, I have to compare the contribution of each country to the total sales in a year. I did this:

SELECT Year, Country, SUM([Total Sales]),
    SUM([Total Sales]) OVER(PARTITION BY Year) 
FROM Table
GROUP BY Country, Year;

Which gives me this error:

Column 'Table.Total Sales' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

You need to nest the sums:

SELECT Year, Country, SUM([Total Sales]),
       SUM(SUM([Total Sales])) OVER (PARTITION BY Year) 
FROM Table
GROUP BY Country, Year;

This syntax is a little funky the first time you see it. But, the window function is evaluated after the GROUP BY. What this says is to sum the sum of the total sales . . . exactly what you want.