Using Case in windowing function ( OVER (Partition))

sql-server, sql-server-2008, window-functions

Solution

You can try this:

SELECT 
   *,
   CASE WHEN (SUM(b.myField) 
OVER (PARTITION BY ID))=1 THEN SUM(b.myField)
 ELSE AVG(b.myField) END 
FROM tbl a
LEFT JOIN tbl2 b ON a.ID = b.aID

Problem

I am trying to accomplish the following: ``` 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 ``` Is this possible with the window functions in SQL Server? I am able to accomplish the following without the case statement: ``` SELECT *, SUM(b.myField) OVER (PARTITION BY ID) FROM tbl a LEFT JOIN tbl2 b ON a.ID = b.aID ```

Original source