Federico Federico - 2 months ago 20
SQL Question

Using GROUP BY and OVER

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

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.