title: Nesting window functions in SQL Server
body: <p>From time to time I'm fiddling around with SQL Server and I get an error akin to
  <code>Can't use a window function on an aggregate</code>.  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 </p>
  
  <pre><code>select   me.patid
          ,COUNT(*) as eligibilityGapsNo
          ,COUNT(*) over(partition by count(*)) 
  from memberEligibility as me
  group by me.patid
  </code></pre>
  
  <p>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?</p>