CODEWITHSUNDEEP
sql-serversql-server-2008window-functions
ryan
ryan

Reputation: 81

Using Case in windowing function ( OVER (Partition))

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

Upvotes: 7

Views: 35478

Answers (3)

Juan Bernardo Gómez
Juan Bernardo Gómez

Reputation: 81

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

Upvotes: 8

Dave.Gugg
Dave.Gugg

Reputation: 6771

I think you'll need to wrap this in a cte in order to get unique results:

CREATE TABLE #temp (ID INT, myField DECIMAL(6,2))
INSERT INTO #temp
        ( ID, myField )
VALUES  ( 1,1 ),(1,2),(1,3),(1,4),(2,2),(2,10),(3,4),(4,4),(4,2)

;WITH cte AS (SELECT  ID ,
        CASE WHEN ID = 1 THEN AVG(MyField) OVER ( PARTITION BY ID )
             ELSE SUM(MyField) OVER ( PARTITION BY ID )
        END AS SumOrAvg
FROM #temp)

SELECT DISTINCT ID, SumOrAvg
FROM cte

DROP TABLE #temp

Upvotes: 0

podiluska
podiluska

Reputation: 51494

You can do this

SELECT *, CASE 
    [something]
    WHEN 1 THEN SUM(myfield) OVER (PARTITION BY ID) 
    ELSE AVG(myfield) OVER (PARTITION BY ID) END 
FROM yourtable

I'm not sure that you should, though

Upvotes: 0

Related Questions