Windowed sum query giving a "because it is not contained in either an aggregate" error

Question

I have the following query:

        select  d.ShortDate, 
                    b.BranchCode,
                    sum(home_net_change_calc) as Period_HomeNet_Denom,
                    sum(oper_net_change_calc) as Period_OperNet_Denom,

                    sum(budget_home_net_change_calc) as Period_BudgetHomeNet_Denom,
                    sum(budget_oper_net_change_calc) as Period_BudgetOperNet_Denom,

                    sum(oper_net_change_calc) over (partition by b.BranchCode order by d.ShortDate rows between unbounded preceding and current row  ) as Range_HomeNet_Denom

                 from FinanceFacts as fact
                   join DimBranch b on fact.BranchKey = b.BranchKey
                   join DimDate d on d.DateKey = fact.DateKey
                where d.ShortDate between '2016-09-01' and '2017-09-30' 
                    and b.BranchCode = '113'
                group by d.ShortDate, 
                    b.BranchCode

And the line with the windowed sum function causes the error: Column 'FinanceFacts.oper_net_change_calc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This doesn't make any sense to me - I am trying to SUM that field, not group by it??

The goal of the query, if it isn't clear, is to sum measures for each ShortDate value, and then also get a total value over all the dates.

I am using SQL Server 2014

score 3 · Answer 1 · answered Nov 08 '17 at 19:34

I was able to find the answer here: Cannot use group by and over(partition by) in the same query?

If I change the offensive line from:

   sum(oper_net_change_calc) over (partition by b.BranchCode order by d.ShortDate rows between unbounded preceding and current row  ) as Range_HomeNet_Denom

to:

    sum(sum(oper_net_change_calc)) over (partition by b.BranchCode order by d.ShortDate rows between unbounded preceding and current row  ) as Range_HomeNet_Denom

Can someone explain how/why this is how it works?

Would really love an explanation on this. It worked for me too. — Michael, Sep 25 '20 at 15:33

AcePL · Accepted Answer · 2018-11-26T11:43:38.590

Windowed functions are applied to the result set of the base query - when SELECTing , but after all the other clauses, like WHERE, GROUP BY and ORDER BY. So when you use OVER clause in the query with grouping, you direct SQL server with that command to operate on it's own set of rows, but on the data set of the whole query.

Thus when you used SUM(oper_net_change_calc) OVER (PARTITION by b.BranchCode), SQL Server interpreted that SUM() as part of the GROUP BY clause, not the OVER() clause, thus you were missing a function to which apply that OVER() clause.

Good explanation of how windowed functions work can be found here: https://www.itprotoday.com/microsoft-sql-server/how-use-microsoft-sql-server-2012s-window-functions-part-1

Thank you - this helped a lot. Even the first sentence made the lightbulb come on! — tember, Nov 20 '18 at 18:40

Windowed sum query giving a "because it is not contained in either an aggregate" error

2 Answers2