title: Using Case in windowing function ( OVER (Partition))
body: <p>I am trying to accomplish the following:</p>
  
  <pre><code>SELECT 
     *,
     CASE WHEN 1 THEN SUM(b.myField) ELSE AVG(b.myField) END OVER (PARTITION BY ID)
  FROM tbl a
  LEFT JOIN tbl2 b ON a.ID = b.aID
  </code></pre>
  
  <p>Is this possible with the window functions in SQL Server?</p>
  
  <p>I am able to accomplish the following without the case statement:</p>
  
  <pre><code>SELECT 
     *,
     SUM(b.myField) OVER (PARTITION BY ID)
  FROM tbl a
  LEFT JOIN tbl2 b ON a.ID = b.aID
  </code></pre>