Multiple PARTITION BY ... OVER the same column in T-SQL

554 Views Asked by At

I found the following query in a book on T-SQL

SELECT
 round(SUM(TotalDue),1) AS Sales,
 LastName,
 FirstName,
 SalesPersonId,
 AccountNumber,
 PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY round(SUM(TotalDue),1))
 OVER(PARTITION BY AccountNumber) AS PERCENTILE_CONT,
 PERCENTILE_DISC(0.4) WITHIN GROUP(ORDER BY round(SUM(TotalDue),1))
 OVER(PARTITION BY AccountNumber) AS PERCENTILE_DISC
FROM
 Sales.SalesOrderHeader soh
 JOIN Sales.vSalesPerson sp
 ON soh.SalesPersonID = sp.BusinessEntityID
GROUP BY AccountNumber,SalesPersonID,LastName,FirstName

I noticed that both PERCENTILE_CONT and PERCENTILE_DISC use the same GROUP definition.

  1. Is there a way to define this GROUP(ORDER BY ...) OVER ... once and share it between both of the calls?
  2. In my code I want to extend this query and calculate six percentiles. Do I need to defined the GROUP every time for each of the columns? If so - is there a significant performance hit because of that?
0

There are 0 best solutions below