Ivan Ivan - 10 months ago 36
SQL Question

Multiple window functions over the same partition

This works fine:

SUM(Revenue) OVER (PARTITION BY Year) AS TotalRevenue


How can I make this work?

SUM(Revenue) - SUM(Cost) OVER (PARTITION BY Year) AS TotalProfit


In other words, it it possible to have a two or more window functions interacting?

Answer Source

Here is one way:

SUM(Revenue - Cost) OVER (PARTITION BY Year) AS TotalProfit

Or:

SUM(Revenue) OVER (PARTITION BY YEAR) - SUM(Cost) OVER (PARTITION BY YEAR) as TotalProfit

These are not exactly the same -- and either could be off due to NULL values. Probably the safest method is:

SUM(COALESCE(Revenue, 0) - COALESCE(Cost, 0)) OVER (PARTITION BY Year) AS TotalProfit
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download