1

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?
LA.27
  • 1,888
  • 19
  • 35
  • 4
    *"Is there a way to define this GROUP(ORDER BY ...) OVER ... once*" No, you must define it in each function all. As shown in the [Basic syntax example](https://learn.microsoft.com/en-us/sql/t-sql/functions/percentile-cont-transact-sql?view=sql-server-ver15#a-basic-syntax-example). – Thom A Dec 16 '21 at 09:20
  • I see. So if I understand correctly, I can't use it in a similar way as some other aggregate functions, e.g. AVG or MIN can share a GROUP BY clause. That's sad, but thanks for saving my time investigating this. – LA.27 Dec 16 '21 at 09:25
  • 1
    *"I can't use it in a similar way as some other aggregate functions, e.g. AVG or MIN can share a GROUP BY clause"* No, they still share the same `GROUP BY` clause, but they can't share the same `OVER` clause (which is what you appear to be asking about). `WITHIN GROUP` means *within* the *group* defined by the `GROUP BY`; and you can only have 1 `GROUP BY` in a statement. – Thom A Dec 16 '21 at 09:28
  • For your query, that group is the grouping set `AccountNumber`, `SalesPersonID`, `LastName` ,`FirstName`. – Thom A Dec 16 '21 at 09:33
  • Got it, thanks. Can you turn your comments in to an answer so that I can accept it? – LA.27 Dec 16 '21 at 09:37
  • 2
    Side-note. I don't think sql server supports named windows like f.e. [mysql 8](https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html) – LukStorms Dec 16 '21 at 09:48

0 Answers0