Applying multiple windows functions to same partition

• it-1119414

  SSC Enthusiast

  Points: 199

  More actions

  November 8, 2018 at 3:05 am

  #372959

  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?

• Thom A

  SSC Guru

  Points: 99143

  More actions

  November 8, 2018 at 3:22 am

  #2012789

  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

• it-1119414

  SSC Enthusiast

  Points: 199

  More actions

  November 8, 2018 at 3:30 am

  #2012790

  Thank you for your reply