O'Reilly logo
  • Dustin Waybright thinks this is interesting:

You can mix detail elements and windowed aggregates in the same expression. For example, the following query computes for each order the percent of the current order value out of the customer total, and also the percent of the grand total.

SELECT custid, orderid,
  CAST(100.0 * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust,
  CAST(100.0 * val / SUM(val) OVER()                    AS NUMERIC(5, 2)) AS pcttotal
FROM Sales.OrderValues;


Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012


The 100.0* is simply to express as a percent 19.26 versus a ratio .1926.