Applying multiple windows functions to same partition

  • I believe you can do this in postgresql but it doesn't seem to be possible in Microsoft SQL Server: if I want to apply multiple windows functions to the same partition, in postgress this can be done using an alias:
    SELECT SUM(fee) OVER f, AVG(fee) OVER f
    FROM Fees
    WINDOW f AS (PARTITION BY agent ORDER BY fee DESC)

    but in sqlserver I would have to repeat the partition expression:

    SELECT SUM(fee) OVER (PARTITION BY agent ORDER BY fee DESC),
       AVG(fee) OVER (PARTITION BY agent ORDER BY fee DESC)
    FROM Fees

    Is there any better way than repeating youself?

  • No, T-SQL doesn't permit the OVER clause to be defined anywhere else other than in the expression that requires it. That will mean repeating it, if you have multiple columns that need the same OVER clause.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thank you for your reply

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply