Windowed functions cannot be used in the context of another windowed

Question

Hi

Error - Windowed functions cannot be used in the context of another windowed function or aggregate.

SELECT
ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo",
A."Account" AS "Account",
(A."Debit") AS "Debit", 
SUM(A."Debit" - A."Credit") OVER (PARTITION BY Account ORDER BY ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account")) AS Balance
From Master A

Thanks

Eliana Blake · Answer

Certainly! The error message you're encountering, "Windowed functions cannot be used in the context of another windowed function or aggregate," typically occurs when you attempt to nest a window function inside another window function within the same query. In the SQL code snippet you provided, the issue lies in the usage of `ROW_NUMBER()` within the `SUM()` window function. SQL doesn't allow nesting of window functions in this manner as it can lead to ambiguous or conflicting results. To resolve this error and achieve the desired functionality, you may need to rethink the logic of your query. One common approach is to use a subquery or a common table expression (CTE) to first calculate the row number and then perform the subsequent calculations on the outer query. Here's a modified version of your query using a CTE to calculate the row number before using it in the `SUM()` function: WITH NumberedRows AS ( SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo", A."Account" AS "Account", A."Debit", A."Credit" FROM Master A ) SELECT "RowNo", "Account", "Debit", SUM("Debit" - "Credit") OVER (PARTITION BY Account ORDER BY "RowNo") AS Balance FROM NumberedRows; By breaking down the operations into separate steps with a CTE, you can avoid the error related to nesting window functions. This revised query first assigns row numbers in the CTE and then uses them in the main query to calculate the balance. I hope this explanation helps you understand the issue and provides a solution to address it effectively. If you have any more questions or need further clarification, feel free to ask!

Windowed functions cannot be used in the context of another windowed

Answers (1)