CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
wootscootinboogie
wootscootinboogie

Reputation: 8695

Nesting window functions in SQL Server

From time to time I'm fiddling around with SQL Server and I get an error akin to Can't use a window function on an aggregate. Which is frustrating because I have SQL Server 2008 and I know that 2012 affords better window function functionality and I use them a lot. However, from time to time I do something like 

select   me.patid
        ,COUNT(*) as eligibilityGapsNo
        ,COUNT(*) over(partition by count(*)) 
from memberEligibility as me
group by me.patid

and this works fine. I assume is it's because I'm already grouping by me.patid but does anyone have a clearer idea of when you can nest window functions like this?

Upvotes: 3

Views: 5458

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You are not nesting window functions here. You are nesting an aggregation function count(*) with a window function count(*) over.

You can nest aggregation functions in window functions. And, I do it. However, I find it clearer to write this as a subquery, because nested aggregation functions just "don't look right" to me:

select patid, eg, count(*) over (partition by egcnt)
from (select me.patid, count(*) as egcnt
      from memberEligibility me
      group by me.patid
     ) t

Upvotes: 3

Related Questions